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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.    

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.