March 23, 2014 at 7:59 am
We are looking at creating a "dashboard" for our customers.
Our customers use our service to submit data to us for various government auditing needs and then we aggregate their data, compare to others on a national level and give them feedback on how they are ranked within their industry segment, etc, etc.
This new dashboard would offer this:
-Current status of each data set if it is submitted, in progress, or not submitted at all yet (customers might submit 4 data sets or 40)
-Errors in the data set (data was submitted and they said they would send data from 5 of their factory sites, yet we only received 3 so far or data from site 2 conflicts with audit data from site 4 so they need to investigate and resolve)
Here is the real question:
IF we run these queries directly against OLAP it takes 3 to 10 seconds per client... that seems like a long time to wait for a page to load.
If all our clients logged in at the same time 8 AM on Monday... they would all be requesting that same 10 second query that touches around 80% of our OLAP tables to scan them to see if data is there, mismatched, etc, etc. Whereas just scanning for all our clients at once doesn't take much more than 3 or 4 times longer than a query for a single client. Seems more efficient to just query for all clients and store the result rather than doing each one on demand.
I was thinking of using SSAS to gather up those results and hold them and then refresh SSAS perhaps every 5/15 minutes or incrementally.
Design this as a data mart:
Customer, factory site, and a list of dimensions for each data set, type of error, and then facts for their status. "DataSet 15: Not Submitted/Submitted No Errors/Partially Submitted" etc...
I haven't worked with SSAS before so I wanted to be sure I wasn't thinking of its purpose completely wrongly.
Thanks.
March 24, 2014 at 7:04 am
SSAS is to store information to be retrieved on a large scale. So in your situation, it seems that it depends. If the data is already in an OLAP format, then I assume the ETL is done for the conversions from an OLTP database. SSAS data must, however, be queried in MDX, not SQL. Do you have that skill?
There are enough open questions that you may want to consider hiring a consulant to give you some guidance.
March 24, 2014 at 8:16 am
It is worth looking at SQL 2014 column-store and in-memory tables. These should give a similar level of performance to SSAS, but could significantly reduce the amount of ETL logic you need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 24, 2014 at 8:25 am
Didn't realize MDX was required, I thought you could use SQL depending on how you configure it?
Yes, columnstore is a good idea, we are looking at 2014 upgrade in around 6 months.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply