I have a complex dataset with more than 16M rows coming from pharmaceutical industry. Regarding the data, it is saved in a sql server with multiple (more than 400) relational tables. Data got several levels of hierachies like province, city, postal code, person, and antigens measures, etc. I would like to create many dashboards in order to observe the changes & trends happening. I can use Pentaho, R (shiny) or Tableau for this purpose. But the problem is data is so huge, and it take so long to process it with dashboard softwares. I have a choice of making cube and connect it to dashboard. My question here is whether there are any other solutions that I can use instead of making a cube? I don't want to go through the hassle of making & maintaining a cube. I would like to use a software where I specify relationships between tables, so the aggregation/amalgamation happens smoothly and output processed tables that can connect to dashboards. I hear Alteryx is one software that can do it for you (I haven't tried it myself, and it is an expensive one!). I understand this task needs two or more softwares/tools. Please share your input & experience. Please mention what tools do you use, size of your data, and how fast/efficient is the entire system, and other necessary details.
3 Answers
We have dashboards that show information about some processes, which have billions of rows in the database. It's not queried directly though, but instead from pre-aggregated data.
We have automated scripts running in database that populate the aggregated data tables specifically for dashboards. In most extreme cases the raw data is so large and comes in at such a high pace that there's a two-tier aggregation in place. First tier will de-normalize the data and 2nd tier will do the actual sum/count type of aggregation.
So you don't need two tools since you can do aggregation purely in database.
edit (answering question from comment): Our dashboards are in Tableau. Data is all in PostgreSQL databases. Automation is done with unix cronjob executing a database function. The function in turn queries which tasks it has to run and runs them. There is a few moving pieces to the whole system, but the architecture isn't too complicated. Write down your ideas and go talk to some architects / engineers - they will know the best way to approach this.
-
$\begingroup$ Thanks @LauriK for your comment. Would you mind elaborating what language or program that automated scripts are written on? I assume you have scripts written on sql. How do you automate them? what software your dashboards are made of? Please give a detailed example as I am trying to adopt something similar to what you have. $\endgroup$– JeanVudaCommented Mar 29, 2015 at 21:26
-
I can't comment due to reputation, but you really need to tell us what version of SQL Server you are running, maybe some more information about how the data is structured and how you're pulling the data into these dashboard. Maybe even how long it's taking and what resources you have available that actually know what they are doing.
OLTP
That said, it sounds like you have a OLTP database with lots of tables. As I don't know the relationships of these tables or how you are pulling the data from these tables, I can only assume you are pulling data from many of these tables. If optimizing the tables is not helping due to how many joins and records you are pulling, then:
SQL Server Analysis Services
It sounds like you need to create a multidimensional database that can be used for reporting. SQL Server Analysis Services helps you do that by allowing you to define OLAP Cubes in many different structures from MOLAP to ROLAP.
Multidimensional Database (Data Warehousing)
Another way is creating a new database that is going to be the foundation for your multidimensional data. Therefore, you would need to create a complex ETL System within SQL Server that converts those 400 tables into facts and dimensions automatically on a daily basis and pushes it into your new database. This is a similar process to what SSAS is going to do for you when you define cubes in SQL Server.
Preaggregate Tables or Views
If you can't do that yourself, then another way is just building new tables in your database that are just preaggregates of the 400 tables that will be used for reporting. Basically established how you are reading the data for your dashboards and find ways to preaggregate that data into fewer tables before you actually pull it into a report.
Automation & Tools
This (as well building multidimensional data) is accomplished simply by creating stored procedures or SSIS packages and automating the process every day. Then Tableau, SSRS or whatever queries the new tables rather than the previous 400 tables that may be slowing the process.
Hire Someone, You Already Have The Tools
The last and final way is finding a tool that does the ETL for you. There are plenty of ETL vendors out there that can possibly address this problem. But keep in mind, you likely have all the tools you need to do this. You just need to hire the talent to do it either temporarily on contract or full-time.
If I had no knowledge of what I was doing in SQL, I would contract a ETL Developer, SQL Developer or BI Developer to help me. Because why buy another tool box when you already have a good tool box available to you?
In this situation, I use RapidMiner (RapidMiner). There a multiple solutions such as Hadoop and Radoop or RapidMiner cloud service etc.
