Search code examples
ssasmdxmdx-query

How to use the equivalent SQL with sub query and where clause in SSAS MDX Cube to create a measure?

I'm trying to "convert" a SQL query to SSAS MDX expression to create a measure in SSAS Multidimensional existent cube.

In SQL I have the table below:

Product ProductCode OperatorName Step StepSequence StepDate StepTimeinDays
Chair Black_Chair Joseph Smith 0_Start 0 02/05/2019 0
Chair Black_Chair Joseph Smith 1_Material 1 02/05/2019 0
Chair Black_Chair Joseph Smith 2_Saw 2 02/05/2019 1
Chair Black_Chair Joseph Smith 3_Build 3 03/05/2019 1
Chair Black_Chair Joseph Smith 4_Paint 4 04/05/2019 0
Chair Black_Chair Joseph Smith 5_Varnish 5 04/05/2019 0
Chair Black_Chair Joseph Smith 6_Packing 6 04/05/2019 1
Table Table_2350 John Doe 0_Start 0 02/05/2019 0
Table Table_2350 John Doe 1_Material 1 02/05/2019 0
Table Table_2350 John Doe 3_Build 3 02/05/2019 0
Table Table_2350 John Doe 4_Paint 4 02/05/2019 0
Table Table_2350 John Doe 5_Varnish 5 02/05/2019 1
Table Table_2350 John Doe 6_Packing 6 03/05/2019 1
Chair Black_Chair Joseph Doe 0_Start 0 03/05/2019 0
Chair Black_Chair Joseph Doe 1_Material 1 03/05/2019 1
Chair Black_Chair Joseph Doe 2_Saw 2 04/05/2019 0
Chair Black_Chair Joseph Doe 3_Build 3 04/05/2019 1
Chair Black_Chair Joseph Doe 4_Paint 4 05/05/2019 1
Chair Yellow_Chair Joseph Smith 0_Start 0 04/05/2019 0
Chair Yellow_Chair Joseph Smith 1_Material 1 04/05/2019 1
Chair Yellow_Chair Joseph Smith 3_Build 3 05/05/2019 0
Chair Yellow_Chair Joseph Smith 4_Paint 4 05/05/2019 1
Chair Black_Chair John Doe 0_Start 0 04/05/2019 0
Chair Black_Chair John Doe 1_Material 1 04/05/2019 1
Chair Black_Chair John Doe 3_Build 3 05/05/2019 0
Chair Black_Chair John Doe 4_Paint 4 05/05/2019 1

In SQL running the query ( provided by Dale in other question specific to SQL ) below:

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;

The return filter the table only with "2_Saw" present:

Product ProductCode OperatorName Step StepSequence StepDate StepTimeinDays
Chair Black_Chair Joseph Smith 0_Start 0 02/05/2019 0
Chair Black_Chair Joseph Smith 1_Material 1 02/05/2019 0
Chair Black_Chair Joseph Smith 2_Saw 2 02/05/2019 1
Chair Black_Chair Joseph Smith 3_Build 3 03/05/2019 1
Chair Black_Chair Joseph Smith 4_Paint 4 04/05/2019 0
Chair Black_Chair Joseph Smith 5_Varnish 5 04/05/2019 0
Chair Black_Chair Joseph Smith 6_Packing 6 04/05/2019 1
Chair Black_Chair Joseph Doe 0_Start 0 03/05/2019 0
Chair Black_Chair Joseph Doe 1_Material 1 03/05/2019 1
Chair Black_Chair Joseph Doe 2_Saw 2 04/05/2019 0
Chair Black_Chair Joseph Doe 3_Build 3 04/05/2019 1
Chair Black_Chair Joseph Doe 4_Paint 4 05/05/2019 1

The final result of this SQL query is ( this is tha same result that I need to get in SSAS cube ):

Step Sum of StepTimeinDays
0_Start 0
1_Material 1
2_Saw 1
3_Build 2
4_Paint 1
5_Varnish 0
6_Packing 1

For example, for the Total Percent measure I used the Expression below and works fine.

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

Now the same "table" are in a SSAS multidimensional cube, I'm trying to get the same result of SQL query in the SSAS expression field to create a measure without success.

I tried to use MDX expression and the more close that I have is below:

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

But this is returning wrong value. Other expressions with parent returned syntax errors.

Solution

  • This was solved in another forum, but the measure could be created without curly braces to get set of data:

     sum ( NonEmpty ( [Product].[OperatorName].[OperatorName] * [Product].[ProductCode].[ProductCode], ([Measures].[StepTimeinDays], [Product].[Step].&[2_Saw]) ), [Measures].[StepTimeinDays] )