Forum Discussion
SSAS Tabular Date Hierarchies not working with Live Connection
Hi v-joesh-msft
My suspicions have been proven correct; line chart forecasting does not work out of the box for a hierarchy mixed with categorical and continuous data types. Even though the sorting in the SSAS Tabular Cube is done on integers and keys.
I would also suggest the degree of flexibility regarding aliasing the field names in the hierarchy is not good enough. For instance, you could use month number in a hierarchy but the option to alias it with another field on the X axis of a line chart would circumnavigate this issue.
Workaround:
To enable forecasting in a line chart, you MUST HAVE a hierarchy solely composed of continuous data. The examples listed above use integers. This presents two issues, firstly field output and secondly how Power Bi presents the prediction.
For example; Month Number is 1,2,3,4 etc and if you select 2 points on your forecasting it looks like 7,8,9,10,11,12……13,14. Using integers exclusively may yield the results you want but the visualisation is not fit for purpose.
Using dates is more effective and allows Power Bi to be more intuitive regarding forecasting. An all Date hierarchy is continuous but will require a second, summarised at month level, date dimension in your cube.
In your first date dimension you’ll need to have a start on month date column i.e 01/04/2016. You’ll use this as a relationship key to your second date dimension.
Your second date dimension should look something like this:
Your code should look something like this:
SELECT
CAST(Cal_Year_Start AS DATE) AS [Cont_Year],
CAST(Fin_Year_Start AS DATE) AS [Cont_Financial_Year],
CAST(Month_Start AS DATE) AS [Cont_Month],
CAST(Quarter_Start AS DATE) AS [Cont_Qtr],
Cal_year AS Cat_Year,
Month_Short_Name AS Cat_Month,
Cal_Quarter_Name AS Cat_Qtr,
Fin_Year_YYYY_dash_YY AS Cat_Financial_Year,
[Fin_Quarter_Qq_Fin_Year_YYYY_dash_YY] AS Cat_Financial_Qtr
FROM Dimension].[Date]
WHERE
(
Day_Of_Year = 1
OR
Month_Day_No = 1
)
This will give you a summarised dataset. Call it something (I called mine Power bi hierarchies),hide all the columns from client tools and join to the first date dimension using the Cont_Month column as the joining key.
Now create the following four hierarchies:
When these are brought into power Bi and used for forecasting the results are improved (not perfect). I have added a forecasting 2 years to this test data:
Year:
Month:
Qtr:
In summary, while this is a far from perfect solution it does look superior to the original approach suggested.
The gridlines and X axis naming conventions are improved but would be greatly improved with either aliasing or more intuition from Power Bi.
Creating the categorical hierarchies allows the users to create more embellished visuals when required.
A well constructed mixed type hierarchy should be supported assuming the correct sorting is done on the cube for the columns.
Up vote this is you have the same issue:
Solution SageHi danial_mcgreevy ,
As you can see, this feature has not yet been added, you can vote in the above idea and add your comments there to make this feature coming sooner.
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-joesh-msft
My suspicions have been proven correct; line chart forecasting does not work out of the box for a hierarchy mixed with categorical and continuous data types. Even though the sorting in the SSAS Tabular Cube is done on integers and keys.
I would also suggest the degree of flexibility regarding aliasing the field names in the hierarchy is not good enough. For instance, you could use month number in a hierarchy but the option to alias it with another field on the X axis of a line chart would circumnavigate this issue.
Workaround:
To enable forecasting in a line chart, you MUST HAVE a hierarchy solely composed of continuous data. The examples listed above use integers. This presents two issues, firstly field output and secondly how Power Bi presents the prediction.
For example; Month Number is 1,2,3,4 etc and if you select 2 points on your forecasting it looks like 7,8,9,10,11,12……13,14. Using integers exclusively may yield the results you want but the visualisation is not fit for purpose.
Using dates is more effective and allows Power Bi to be more intuitive regarding forecasting. An all Date hierarchy is continuous but will require a second, summarised at month level, date dimension in your cube.
In your first date dimension you’ll need to have a start on month date column i.e 01/04/2016. You’ll use this as a relationship key to your second date dimension.
Your second date dimension should look something like this:
Your code should look something like this:
SELECT
CAST(Cal_Year_Start AS DATE) AS [Cont_Year],
CAST(Fin_Year_Start AS DATE) AS [Cont_Financial_Year],
CAST(Month_Start AS DATE) AS [Cont_Month],
CAST(Quarter_Start AS DATE) AS [Cont_Qtr],
Cal_year AS Cat_Year,
Month_Short_Name AS Cat_Month,
Cal_Quarter_Name AS Cat_Qtr,
Fin_Year_YYYY_dash_YY AS Cat_Financial_Year,
[Fin_Quarter_Qq_Fin_Year_YYYY_dash_YY] AS Cat_Financial_Qtr
FROM Dimension].[Date]
WHERE
(
Day_Of_Year = 1
OR
Month_Day_No = 1
)
This will give you a summarised dataset. Call it something (I called mine Power bi hierarchies),hide all the columns from client tools and join to the first date dimension using the Cont_Month column as the joining key.
Now create the following four hierarchies:
When these are brought into power Bi and used for forecasting the results are improved (not perfect). I have added a forecasting 2 years to this test data:
Year:
Month:
Qtr:
In summary, while this is a far from perfect solution it does look superior to the original approach suggested.
The gridlines and X axis naming conventions are improved but would be greatly improved with either aliasing or more intuition from Power Bi.
Creating the categorical hierarchies allows the users to create more embellished visuals when required.
A well constructed mixed type hierarchy should be supported assuming the correct sorting is done on the cube for the columns.
Up vote this is you have the same issue: