Speed and space considerations in SQL Server Analysis Services

I have an existing hierarchy in a dimension and I want to insert a level in the middle of the hierarchy.  An analogy would be that I have a Fiscal Year-Fiscal Month hierarchy and I want to insert a Fiscal Quarter level in the middle.

I'm trying to analyze the impact on the database from a size and speed perspective.  

If no aggregations are saved in the database then will the insertion of a level in the hierarchy take up more disk space?  
What will be the effect on query speed with this extra level?  Will a Fiscal Year aggregated value take longer to retrieve from a measure group with the additional child in the hierarchy?
What factors should I consider to estimate the effect (number of distinct values in the new level, size of the dimension table, etc.)?


P.S.  First time on the site so I'm not sure how many points to attach to this.
Who is Participating?
tpi007Connect With a Mentor Commented:
By adding another heirachy level then in theory the cube will use marginally more space and longer to build. The extra level means SSAS can calculate and store data in its cache at Quarter level when queries are returned to a client so having plenty of RAM helps. The standard heirachy on a time dimension which i use are Year > Quarter > Month > Week > Date. Obviously the heirarchy levels should refelct your  business and reporting needs. All data in SSAS returned to client are retrieved from cache so adding an extra dimension will help with data retrieval for clients. We moved to having just a heirarchy for end user reporting and not having the standard dimension attributes as seperate which simplified report developement and ensure end users only used the heirarchy for report filetring.
If you add Quarter then when calculating Year totals can sum up from Quarter cell values. This will mean that there are less cells to calculate as there as less Quarters than Months. You can also use methods suich as cache warming which runs an MDX query that will will pre-populate cache with common data; ususally done after a cuibe build and prior to business users accessing the system as data returned to clients comes from cache.users will also be able to view reports and drill down to quarters using eCube browser and excel e.t.c. Heriarchies will allow you to easily utilise Parallell Periods for Year on Year comparison calculations in cube easily with a generic calculation for any heirarchy level; many articles on net about this subject.    
In regard of Aggregates; it depends on how often the data is queried; the options are
  1. no aggregation = shorter cube build time and longer data retrieval time  
  2. aggregates = longer cube buld time and quicker data retrieval .
You have to consider the 2 options up between frequency of queries versus cube build time. I dont believe there is a scientific way to calculate this but requires trial and error to determine optimal setting.    
You can check size of cube by viewing the total size of the folder that the cube resides in windows; you can then view any changes to size that your modfications have had on the cube size. You can determine cube location by viewing the default location in SSAS properties window. Business constraints can affect your choice; availability of the cube to business users at early time may be more important than speed of queries to end users.    
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.