Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert MDX query result to SQL table Expand / Collapse
Author
Message
Posted Thursday, August 7, 2014 10:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 5, 2014 6:26 PM
Points: 23, Visits: 75
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

Post #1601020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse