Reply
Frequent Visitor
Posts: 9
Registered: ‎06-30-2016
Accepted Solution

Comparing dates using SSAS cube

Hi there,

 

I've been searching for days without finding a solution for this problem, so here it goes:

 

There's two ways of using a SSAS cube to browse data and create charts: a live connection or by importing the data. The live connection works great, but has some disadvantages. The biggest one for me is that I can't create calculated measures. For example, I want to compare sales of a month in the current year with the month of the previous year. However, since I can't write any DAX queries this seems impossible? It's just one example of the things I can't seem to achieve without creating calculated measures.

 

So I tried importing the data, which does allow me to create calculated measures. Since I have a big cube this takes a long time to load for starters. But next to that, and more important to me, it doesn't recognize relationships and hierarchies from my cube. Since ssas hides the foreign keys in the fact tables I don't know how to manually assign them either.

 

So to sum it up: Is it possible to, for example, compare sales over different years without using calculated measures? If not, how can I achieve this functionality for my SSAS cube without losing my relationships?


Accepted Solutions
Established Member
Posts: 135
Registered: ‎05-09-2016

Re: Comparing dates using SSAS cube

MarnikV - The idea behind live connection for SSAS is that you will not be using DAX features for Powre BI instead all the transformations will be done using MDX for SSAS.

 

Now you have only choice to create those transformations within your Cube.

View solution in original post


All Replies
Frequent Visitor
Posts: 9
Registered: ‎06-30-2016

Comparing dates using a SSAS cube

Hi there,

 

I've been searching for days without finding a solution for this problem, so here it goes:

 

There's two ways of using a SSAS cube to browse data and create charts: a live connection or by importing the data. The live connection works great, but has some disadvantages. The biggest one for me is that I can't create calculated measures. For example, I want to compare sales of a month in the current year with the month of the previous year. However, since I can't write any DAX queries this seems impossible? It's just one example of the things I can't seem to achieve without creating calculated measures.

 

So I tried importing the data, which does allow me to create calculated measures. Since I have a big cube this takes a long time to load for starters. But next to that, and more important to me, it doesn't recognize relationships and hierarchies from my cube. Since ssas hides the foreign keys in the fact tables I don't know how to manually assign them either.

 

So to sum it up: Is it possible to, for example, compare sales over different years without using calculated measures? If not, how can I achieve this functionality for my SSAS cube without losing my relationships?

Established Member
Posts: 135
Registered: ‎05-09-2016

Re: Comparing dates using SSAS cube

MarnikV - The idea behind live connection for SSAS is that you will not be using DAX features for Powre BI instead all the transformations will be done using MDX for SSAS.

 

Now you have only choice to create those transformations within your Cube.

Moderator
Posts: 2,107
Registered: ‎03-06-2016

Re: Comparing dates using SSAS cube

@MarnikV

 

In this scenario, if you need to keep the relationships, you have to use live connection. Otherwise when you use Import mode, the measures, dimension members will be resolved as data fields  and retrieved into same dataset. Since you can't creating calcualted measure within Power BI Desktop under live connection, I suggest you build those caluclated measures within cube, they can be loaded into Power BI properly. 

 

Regards,

Regular Visitor
Posts: 21
Registered: ‎04-04-2017

Re: Comparing dates using SSAS cube

In my molap cube I have some calculated measures that return amounts for YearToDate, YearToMonth, YearToDatePreviousYear based on a date hierarchy. The calculated measures are written in mdx.

 

Example for measure “Sale Net Net Y-1”

 

AGGREGATE(

    PARALLELPERIOD(

        [Calendar].[Hierarchy].[Year],

        1,

        [Calendar].[Hierarchy].CurrentMember

    )

    , [Measures].[Sale Net Net]

)

 

 

These measures work fine in an Excel pivot tables but don’t when using PowerBi slicers

 

Excel :

 

Pivot Table (with comments in yellow)

 a.png

 

PowerBI :

 

Using the slicers or filters doesn’t work

The new matrix preview visualisation works but is not user friendly

 

b.png

 

 

Many thanks in advance

David