Hi all,
I have a quick question regarding the processing time.
I created view in db for the count purposes with huge fact table. ( I'm using dimension table which
joins the 1 fact and 1 dimension to count the key for the fast data retrieval)
If I write the query for the count in management studio and it gives a results within 5 sec.
But, if I add this view in DSV and process the cubes than it takes around 50 minutes.
This causes due to the Fact table? or what else?
I don't know what to check and please give me some comments.
Thanks in advance.
Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||
Hi Darren,
Thanks for your good tips.
As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.
So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.
Please give me some comments.
Thanks.
|||There would be a couple of possible approaches to improving the processing performance.
One would be to partition the fact table so that you only process recent records and not the full 153 million.
Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.
Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.
