Home Forums Data Warehousing Analysis Services Cube Designs - how many, how do you deal with size and timeframe? RE: Cube Designs - how many, how do you deal with size and timeframe?

  • You can have multiple partitions for one measure group. You have to be careful with data overlapping though - query-bound partitions are not aware of each other. So for example if you had one partition which held data from 01/01/2015 - 07/01/2015 and another for 06/01/2015 - 14/01/2015 then the measure would double up the count for 2 days.

    In terms of size, I don't think you have a problem. Your dimensions are your dimensions and none of them sound massive. When you are talking about all of the different levels you are thinking as if everything would be processed and stored as an aggregation. Whereas you can do this, don't. It's better to pick and choose your aggregations to keep the performance up and the storage down. Here's what I do:

    I build the cube (with all of the design considerations needed) and process it without aggregations

    I turn on the OlapQueryLog table (more info here: http://www.mssqltips.com/sqlservertip/2876/improve-sql-server-analysis-services-performance-with-the-usage-based-optimization-wizard/)

    I take 10-20 key reports of the business and recreate them using the cube data in excel.

    I then use the usage based optimization wizard to generate aggregations based on the queries I've just been running.

    I then use BIDS Helper Aggregation Manager (https://bidshelper.codeplex.com/wikipage?title=Aggregation Manager) to manage any duplicates/generally tidy up. I also use this to add any "special" aggregations I feel should be there but for whatever reason are not.

    That will give you a good base set of aggregations to start with. Run the usage based optimization every once in a while to make sure that you're picking up the relevant usage patterns.

    To be honest you really can't go far wrong with the book that you are reading 🙂


    I'm on LinkedIn