Insert MDX query result to SQL table

• MaggieW

  SSC Eights!

  Points: 947

  More actions

  August 7, 2014 at 10:43 pm

  #147049

  Dear all

  I'm new to SSAS and MDX and need your help on my current project. What I need to do is to insert the MDX query result to sql table.

  Here's the scenario:

  Let's suppose the data is from [AdventureWorksCube] the Measure is [Net sales], I need to pull out the net sales data for certain prodcuts and use it to calculate the rebate amount by multiplying cetain rebate percentage.

  Here's a sample MDX query under Management studio:

  select {[Measures].[Net Sales]} on 0,

  {[Account].[Bill to account]} on 1

  from [AdventureWorksCube]

  where ([Product].[productCategory].[Product Code], [Date].[Month Year].[Aug-14], [Scenario].[Scenario1], [Factory].[FactoryID])

  The output of the query will be like what's in below:

  BillTo Net sales

  345353 $79

  536261 $829

  489202 $9303

  573982 $8933

  I need to insert the query result along with all the members in the where clause to a SQL table in below.

  The fact is that one BillTo might contain sales amount for multiple product code

  BillTo Netsales ProductCode MonthYear Scenario FactoryID

  345353 $79 1 aug-14 1 01

  536261 $523 2 aug-14 1 02

  536261 $306 3 aug-14 1 01

  489202 $5303 4 aug-14 1 02

  489202 $2000 5 aug-14 1 01

  489202 $2000 6 aug-14 1 02

  573982 $2220 7 aug-14 1 01

  573982 $4000 8 aug-14 1 01

  573982 $2713 9 aug-14 1 02

  Question #1: how to populate the above table to include the query result and members in where clause

  Question #2: how to use parameters to make the query dynamic? The above table needs to be loaded monthly with every month's transaction with different productcode

  and different