Vision Binary

There was an issue with cube processing with my current client, we have two cubes, one with 80 millions rows fact table, the other has 10 millions row fact table.before May everything was fine, the cube was processed twice a month, and in the weekends, both of them will take 18 hrs total, it’s very long but my client was Okay with it.

Last time when they try to process the cube again, it took forever, when Monday morning we all back at office, the thing was still running. So I was asked for take a look and help them to enhance the performance. I check the whole structure, and also read the message, the process got stuck at a selecting query, which is a view. and that’s is one of our dimension. I keep checking the data source view of the cubes, found out , the two fact tables are using views plus calculated fields, which could be the major  problems of the processing time. Then I talked to our manager about it, he said the reason why not index the tables is because the tables, which are the data source, get truncated then insert them with the data from another database in another server every time before they process the cubes. with indexes, of course the inserting would probably take very long.

So I decide to do something different. And I can tell you the result is great, before was 18 hours plus, now just 3 hours after I modified the cubes.

• I inserted into the tables I need in my working server database from the source server tables.
• Index the tables.
• Modify the SSIS package as incremental load.(which using “except select ” for inserting, and “update”for some columns)
• I need use physical table instead of views so I created tables based on the views. Then insert rows into the tables.(For later easier modify cubes, the tables should named the same according your view’s name, before create table you need too slightly change the view’s name)
• Indexed the tables I created based on views.(It actually didn’t take long, the longest one took me 2 minutes.)
• creates foreign keys  in the columns of the tables I will use in the cubes.
• Last step, click the data source views, and in the design panel,right click the table in the list, choose ‘replace table’–‘with another table’, then choose the table you want to replace the view)
• delete the one in analysis service, rebuild your cube.
• Process the dimensions first.
• Then process the measure groups.

I told my manager I modified something in their SSIS and also the cubes, he is still happy about it, because hey, now they can process cubes any night they like.

And this thing just further prove that: always index your table properly and you will save a lot of time.