This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(80, 98%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EK%3C/text%3E%3C/svg%3E)
Dear Experts,
would like to understand the relation between Time Hierarchy vs SSAS Cube Cache.
My cube is having time hierarchy as per below.
Year >>> Quarter >>> Month.
Whenever I query the SSAS Cube from excel, first time it takes 12 secs to fetch the data for 1 year (month wise) and next time onwards it will take 2 secs and reusing the cache. Which is good. Now the challenge I am facing if user select year 2022 and one quarter from 2023, the cache is not at all working, every time it's taking 18 secs to fetch the data, it's like any 1 or 2 full years and quarter from other year, it's not reusing cache. Is there any way to fix this?
would like to understand the relation between Time Hierarchy vs SSAS Cube Cache.
"Time hierachy" is just a business logic, beside it's a dimension as every else.
Have you create aggregations on the facts?
Yes Olaf, i do have aggregations on fact measure group.
Any thoughts ?
I will provide additional details to give more picture on issue that i am facing.
After we connect the cube from excel, when I select 2 years information first the query is taking time and next time onwards reusing the cache. However instead of 2 years if i select FY22(Full Year) + One quarter from 2nd year, it's not at all reusing the cache every time I run it's taking data from disk and calculating the measures. Below is the MDX query that captured while refreshing the excel.
SELECT NON EMPTY Hierarchize({[Dim EIS Time].[By Fiscal Year].[Period].AllMembers}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Dim EIS Time].[By Fiscal Year].[Quarter].[EIS PERIOD YEAR],[Dim EIS Time].[By Fiscal Year].[Period].[EIS QUARTER NAME],[Dim EIS Time].[By Fiscal Year].[Period].[PERIOD NUM EIS] ON COLUMNS , {[Measures].[Measure1],[Measures].[Measure2],[Measures].[Measure3],[Measures].[Measure4]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM
(SELECT ({[Dim EIS Time].[By Fiscal Year].[Quarter].&[20223], [Dim EIS Time].[By Fiscal Year].[Year].&[2.021E3]}) ON COLUMNS FROM [Financial Power Cube])
I see similar issue from other user. Not sure whether MS need to tune the way MDX query get prepared from excel! especially when user applies selection at parent & child level filter.
I am bit surprised, anyone else not facing similar problem.