cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danial_mcgreevy
Frequent Visitor

SSAS Tabular Date Hierarchies not working with Live Connection


Technical Details:
Data Source: SSAS Tabular, 1200, 13.2.5492.2
VS version: Professional 2017 15.9.15
PowerBi Version : 2.72.5556.701 (19.08) (x64)

 

Issue:
When using a live connection to a SSAS cube the dates (from our date dimension) in power bi does not allow us to use our hierarchies for trend or forecasting analysis. It was also appear that the in-built date hierarchies do not work in live connection mode.


The formatting in power bi suggests that the dates have been imported as strings.

 

Further Details:


The cube uses a date dimension that has 2 hierarchies, Calendar and Financial.

The date dimension is marked as a date table and the unique identifier column is a date.

Conclusion:


I’m surprised this does not work out of the box. Excel and SSRS does not give have the same issues. I’ve read several posts about a similar issue so I’m guessing my organisation is not the only one who needs this to work.


I’ve tested the import mode and while this is an improvement our datasets are too large for this.


Direct query on the back end SQL gives us greater flexibility but this is not fit for our purpose. Interestingly, in direct query mode the fields pane does not show that a column is a usable date. But when I edit the query it is marked as a date. 


I need to know:
Are all dates imported as text in a live connection? If this is the case how can we get the date logic working effectively for forecasting?


Are tabular data hierarchies supported in live connection mode? If not, when, why and what kind of work arounds have surfaced?

 

 

1 ACCEPTED SOLUTION

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:

 

dates.PNG

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:

 

hierachies.PNG

 

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:

Year.PNG

Month:

month.PNG

Qtr:

Qtr.PNG

 

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:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33377071-date-with-no-hierarchy-w...

 

 

View solution in original post

9 REPLIES 9
v-joesh-msft
Solution Sage
Solution Sage

Hi @danial_mcgreevy ,

Referring to this post: Date with no hierarchy wit ssas tabular live connection

"When we create a live connection to SSAS, the date type field will be treated as string which can’t be changed in Power BI with direct query mode."
I did a test, the forecast function is available, what version of your desktop is? Try to upgrade the desktop to the latest version to see if it works.

11.PNG

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.

@v-joesh-msftThank you for coming back to me so quickly.

 

I should have mentioned that using a date (formatted a date type in SSAS) will work with forecasting. 

 

Untitled.jpg

 

It's when we drill down into a hierarchy (already created on the cube) that we encounter the issue mentioned in the original post.

 

iUntitled2.jpg

 

 

If this is as simple as upgrading then great.  But before we upgrade can you confirm the following:

 

Tabular cube created hierarchies can be used for forcasting metrics in a live connection environment.

 

Kind regards

 

Danial

 

Hi @danial_mcgreevy ,

My date hierarchy forecasting is working properly:

21.PNG22.PNG

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.

@v-joesh-msft 

 

That looks great.  Can you confirm that is from a tabular cube?  Also, was this a known issue in previous versions?

 

We're not on the November build yet becasue we have to be in line with the Power Bi Server editions. 

 

Thanks

Hi @danial_mcgreevy ,

Yes, I use the tabular cube. Sometimes due to the version, there will be errors. I tested it in the October version and the November version, and everything works fine.

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.

@v-joesh-msft 

I have downloaded the October version and it is still not showing a forecast for all layers of the hierarchy.

 

My hierarchy has diffent logic to yours,.  You appear to have all integers:

Year: Whole Number 

Qtr: String (ordered by Whole Number )

Month: String (ordered by by Whole Number )

Day Date (ordered by Key)

 

Year and Day work fine and give us the results as expected.

 

Is this is limitation of hierarchies in power bi? 

 

Do they all need to be numerical values to work? 

 

The hierarchies created under import mode seem to work well strings but they use a Date column that has to be formatted as a date in Power Bi to work.  This is why i had a hunch about all the dates being imported as string using a live connection.

 

Can you please confirm if you can use strings, i.e. Qtr Name and Month Name in a tabular date  hierarchy under a live connection.

 

Your help and time is appreciated.

 

Kind regards

 

Danial

 

 

 

 

 

 

@v-joesh-msft 

This is my issue:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33377071-date-with-no-hierarchy-w...

 

Has this been remedied or do you need a to use all integers in your hierarchy for continous analysis?

 

This is the type of hierarchy i want in power bi and it has to be able to do forecasting and trend analysis:

 

Untitled3.jpg

 

But at the moment i have to do this to be able to do forecasting and trend analysis:

Untitled4.jpg

 

This is clearly not informative enough for the end user.

 

Thanks

 

Danial

Hi @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:

 

dates.PNG

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:

 

hierachies.PNG

 

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:

Year.PNG

Month:

month.PNG

Qtr:

Qtr.PNG

 

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:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33377071-date-with-no-hierarchy-w...

 

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.