MS SSAS - Calculated member measure to show the move from yesterday to today

Leigh Tilley 21 Reputation points
2022-11-24T17:39:22.71+00:00

Hi

I have a field called T that will either be T1 or T2 for a cube that contains two days worth of data. Rolling so it only has the last two days worth of data.

I thought this would work but it doesn't / only shows the value on the day it actually has T2 / T1:

(([Deal].[T].&[T1], [Measures].[Notional_SUM]) - ([Deal].[T].&[T2], [Measures].[Notional_SUM]))  

Or simply showing Notional.T2 doesn't work / only shows for the day that has T2.

([Deal].[T].&[T2], [Measures].[Notional_SUM])  

I mentioned before that this is a clone to replace a Java in-memory cube and obviously that can do funky stuff like queries at leaflevel to get both and do the diff calc etc.

How do I pull this off in SSAS please? I note that I cannot use WHERE

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,261 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2022-11-24T18:41:57.107+00:00

    You're asking the cube for the value corresponding to T2 for the Deal.T coordinate, all other coordinates staying whatever they are for the respective cell, and the cube duly obliges. Not its fault if the value is blank.
    Mind letting us know what you actually wanted to achieve? ;-)

    0 comments No comments

  2. Leigh Tilley 21 Reputation points
    2022-11-25T08:18:10.157+00:00

    Hey

    Long time no speak; I hope you are well. :)

    The project is nearly done with jus these last two things to fix I think...oh and it seems Drillthrough not working for calculated members...but I was reading a guide on adding a variable set to NULL in the DSV then setting that in the MDX script?

    Anyway, let's focus on these 2 first! :)

    There are 2 days worth of data and on T1, the main business day, the users are used to seeing:

    Notional_SUM | Notional_LONG | Notional_SHORT | Notional_MOVE | Notional_T2

    Notional_SUM: core measure in the fact table and works as usual/expected

    Notional_LONG: to do this one I added a flag at load time depending on the direction of notional (P for positive, N for negative) and do ([Deal].[NotionaDirectionFlag].&[P], [Measures].[Notional_SUM)

    Notional_SHORT: to do this one I added a flag at load time depending on the direction of notional (P for positive, N for negative) and do ([Deal].[NotionaDirectionFlag].&[N], [Measures].[Notional_SUM)

    Notional_MOVE: Need to show the result of (T1 of Notional_SUM) - (T2 of Notional_SUM) to show the difference/move hence my example in first post of:
    (([Deal].[T].&[T1], [Measures].[Notional_SUM]) - ([Deal].[T].&[T2], [Measures].[Notional_SUM]))

    Notional_T2: Always show the T2 value of Notional_SUM


  3. Leigh Tilley 21 Reputation points
    2022-11-28T17:38:12.217+00:00

    Hey hey

    I hope you had a great weekend.

    I sorted a few other data things out and rebuilt everything. I moved T back to the Date dimension. I've ensured it has ID as its key. I'm still not achieving the behaviour we've discussed. I am sure it is something small that I've missed with SSAS and the design.

    264901-1.jpg

    264911-2.jpg

    264780-3.jpg


  4. Leigh Tilley 21 Reputation points
    2022-11-29T12:14:22.113+00:00

    Hi

    I changed the Date from Date value to an INT ID as SSAS Design in Visual Studio kept telling me that for so many rows to use INT keys instead.

    So I followed its advice. So in the fact tables there's now two INTs for the Date instead of the date value, which is what I began with ages ago.

    I did try changing T to use Date as its key but each time I tried to deploy the process failed until I reverted; so now it's at its default setting when you add a new attribute.

    0 comments No comments

  5. Leigh Tilley 21 Reputation points
    2022-11-29T12:42:07.577+00:00

    Hey

    Great so I knew you'd point me in the right direction. As I've learnt this by trial and error over the last few months since we spoke and I hadn't actually really looked at the Attribute Relationship tab. I'm still checking some parts out.

    I tweaked it to this and now behaviour is as desired:

    265271-dim-attr-rel.jpg

    I've got all dimensions and measures in place and this was one of the last behaviours to sort out.

    I've got a classic setup of:

    SQL Agent morning job

    • Stored proc that does the data prep
    • Cube process

    So actually the only unknown for me, and I alluded to it in an earlier comment, was that I went to execute a drillthrough (I've made a custom drillthrough and my custom C# MDX XLL/DLL setup can call it successfully as well as standard MDX queries) on a calculated member and I was informed it's not possible! :)

    I did read, on another MS forum/ticket, that a workaround is to add a new measure/variable set to NULL in the DSV then set the Calculated script to set this. I wanted your expert opinion on how to fix drillthrough on calculated member please. :)