question

ito-8081 avatar image
0 Votes"
ito-8081 asked AlexeiStoyanovsky commented

SSAS Cube MDX - How to create a mesure in Calculations Expression field like a subquery?

I'm trying to create a measure in SSAS Cube with MDX, to understand my point it is more easier explain based in source ( SQL ).

I have a table that it is loaded in cube. This table have steps sequence like a transaction table ( not all steps are mandatory ):

117581-capture.png


In the cube I have a measure to sum the StepTimeinDays and other measure to calculate the percent of StepTimeinDays:

 ([Measures].[StepTimeinDays],[Product].[Step].CurrentMember)/([Measures].[StepTimeinDays],[Product].[Step].[All])

The measure to sum StepTimeinDays are ok, returning the expected result in cube by step:

117489-capture1.png

I need to create a measure that return StepTimeinDays for all Steps only for process where step have the step "2_Saw" in the cube.

To clarify, in SQL is the same to return with "filter" in the transaction table only the data with step "2_Saw" in the all product process for each product like this:

117437-capture2.png

And will return the result ( this is that I need to return in the measure in Cube ):

117530-capture3.png

I could return the result expected only in SQL with the query:

 select Step
     , sum(StepTimeinDays) as SumofStepTimeinDays
 from dbo.build_process t1
 where exists (
     select 1
     from dbo.build_process t2
     where t1.ProductCode = t2.ProductCode and t1.OperatorName = t2.OperatorName
     and t2.Step = '2_Saw'
 )
 group by Step
 order by Step asc;

Does anybody knows how to retun the same as this query in the SSAS Cube Measure?


sql-server-analysis-services
capture.png (54.9 KiB)
capture1.png (5.8 KiB)
capture2.png (32.3 KiB)
capture3.png (5.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ito-8081, welcome to Microsoft Q&A! We have not received an reply from you now. Could you please anwser the questions from AlexeiStoyanovsky?

0 Votes 0 ·

1 Answer

AlexeiStoyanovsky avatar image
0 Votes"
AlexeiStoyanovsky answered AlexeiStoyanovsky commented

Judging by your SQL query, the process is implicitly defined in the data table by a combination of product code and operator name. The question is, how it is defined in the cube. Basically, you get a set of processes that are nonempty against ([Measures].[StepTimeinDays],[Product].[Step].&2), and then you just sum [Measures].[StepTimeinDays] over this set.

· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AlexeiStoyanovsky, this is the problem for me, I don't know how I can get a set of processes against ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw]) in cube expression like in the SQL Query.

I research in the internet but the almost of content is related to SSAS Query using SSMS. I tried to execute for example the expressions in the calculations tab of the cube:

Considering the sum of StepTimeinDays (that is ok) and the sum of StepTimeinDays only for process step 2:

 ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw].CurrentMember.Parent.Parent)

 ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw].CurrentParent)

 ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw],EXISTS([Product].[Step].[All]))

 ([Measures].[StepTimeinDays],[Product].[Step].[All].CurrentMember,EXISTS([Product].[Step].&[2_Saw]))

 ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw].CurrentMember,EXISTING([Product].[Step].&[2_Saw]))

All these expressions returned the same in Cube Browser:

117982-capture.png

The expression below return only the result for Step 2 for all other steps

 ([Measures].[StepTimeinDays],[Product].[Step].&[2_Saw])


118021-capture1.png



0 Votes 0 ·
capture.png (14.2 KiB)
capture1.png (14.2 KiB)

Playing around with the Step attribute of the Product dimension won't help you here. You need the process, which could be identified by the two aforementioned fields or perhaps by a surrogate key. How are Product Code and Operator Name represented in your cube?

0 Votes 0 ·

The Product Code and Operator Name are in the dimension "Product", the same dimension of Step. The cube was not created follow the best practices, the "Product" dimension don't have a hierarchy.

The cube shows the dimension Product, and inside this dimension I have:

117960-capture.png



0 Votes 0 ·
capture.png (5.3 KiB)
Show more comments