Convert MDX to SQL statement

user_2470603 0
Viewed 2106

I'm using SQL server 2016.

I am new to MDX. Is it possible to convert a MDX statement to SQL? Is there a converter?

The below is my MDX - how do I convert this to SQL?:

WITH MEMBER _x AS   
IIF( [Measures].[Inherit from Material Group] <> 0, 
[Measures].[Splitting Percent at Material Group m], 
[Measures].[Splitting Percent at SKU m] )  
SELECT _X  ON 0,  NON EMPTY (  
[Product].[Sales Org SKU].[Sales Org SKU],  
[MARKET].[Sales Org Ship to Party].[Sales Org Ship to Party]  
) ON 1 FROM TEST
mdx ssas tsql sql-server
1 Answers

You need to understand that you can have an equivilant of MDX query in SQL but , the SQL version will run on the dimension Model/ Star schema that feeds your SSAS solution.

In the below translation I have supposed the following about your dimensional model/ star schema

  1. Your Fact table name is "yourfactTable" and it contains the following columns a)Productkey which is a foreginkey. b)MarkeyKey which is a foreginkey. c)The following three columns are facts and are numeric in nature.[Inherit from Material Group],[Splitting Percent at Material Group m],[Splitting Percent at SKU m] 2)
  2. You have a dimension named Product. The primarykey of this table is productkey. It also has a column [Sales Org SKU]
  3. You have a dimension named Markey. The primarykey of this table is marketkey.It also has a column [Sales Org Ship to Party]

Based on this take a look at the following query

 select [Product].[Sales Org SKU],
    [MARKET].[Sales Org Ship to Party],
    case when sum([Inherit from Material Group])<>0 
    then sum([Splitting Percent at Material Group m]) 
    else sum([Splitting Percent at SKU m]) end as  _x 
    from
    yourfactTable f
    inner join 
    [Product] p
    on f.productkey=p.productkey
    inner join 
    [MARKET] m
    on f.marketkey=m.marketkey
    group by 
    [Product].[Sales Org SKU],[MARKET].[Sales Org Ship to Party]
user_97000350
Related