MDX results to Excel in Power Pivot

Question

I don't know ABCDs of MDX. I have this query that was handed over to me by my predecessor and that it is needed only once in a year! That time of the year happens to be now! The query runs and returns the results. However, I am unable to copy the result along with the rows and column names from the SSAS result window.

Googling I found the options of Linked servers, setting query options to save the result as csv etc, using SSIS packages and PowerPivot.

The first two are not possible because of restrictions on the DB. I need to go over a bunch of corporate hurdles to get the necessary permissions.

SSIS packages and Power Pivot - have the same issue. 'BUD' is a named set and it is referenced in the filter below in select statement. Both SSIS (when typed in OLEDB Source) throws error - The dimension [BUD] was not found.

Working with PowerPivot, I executed set create statement separately and then pasted only the code after 'WITH'. The Error was -The dimension [BUD] was not found.

Any help of how to reference the BUD in the select statement for Power Pivot or SSIS? I am using sqlsever2008r2.

This the procedure -

create set [DB1].BUD AS
nonemptycrossjoin(

    {[Market].[Markets].&[Europe].children,[Market].[Markets].[Part of World].&[Africa].children},
    [Product].[PL].&[CD] : [Product].[PL].&[KZ],
    [Plant].[Plant].children
) go


with 

member measures.callsCY1 as
/* Lot of measure calutaions here */

select 
non empty
{
[Measures].[Qty Sold],
callsCY1,costCY1,MTRLcostCY1,LabourCostCY1,
SCR_C,callsRY1,costRY1,MTRLcostRY1,LabourCostRY1,
callsCY2,costCY2,MTRLcostCY2,LabourCostCY2,
callsRY2,costRY2,MTRLcostRY2,LabourCostRY2,
callsCY3,costCY3,MTRLcostCY3,LabourCostCY3,
callsRY3,costRY3,MTRLcostRY3,LabourCostRY3
}
*
{[Report Period].[Report Periods].[Quarter].&[2013-01-01T00:00:00] : [Report Period].[Report Periods].[Quarter].&[2014-06-01T00:00:00]}
on 0,
non empty
filter(
bud, [Measures].[Qty Sold] <> 0 OR [Measures].[QTY Service Calls] <> 0)
on 1
from [db1]

whytheq whytheq · Accepted Answer · 2014-06-19T15:24:55

Does bud need to be seperate? Can't you just use this?

with 
set [BUD] AS
nonemptycrossjoin(

    {[Market].[Markets].&[Europe].children,[Market].[Markets].[Part of World].&[Africa].children},
    [Product].[PL].&[CD] : [Product].[PL].&[KZ],
    [Plant].[Plant].children
)
member measures.callsCY1 as
/* Lot of measure calutaions here */

select 
non empty
{
[Measures].[Qty Sold],
callsCY1,costCY1,MTRLcostCY1,LabourCostCY1,
SCR_C,callsRY1,costRY1,MTRLcostRY1,LabourCostRY1,
callsCY2,costCY2,MTRLcostCY2,LabourCostCY2,
callsRY2,costRY2,MTRLcostRY2,LabourCostRY2,
callsCY3,costCY3,MTRLcostCY3,LabourCostCY3,
callsRY3,costRY3,MTRLcostRY3,LabourCostRY3
}
*
{[Report Period].[Report Periods].[Quarter].&[2013-01-01T00:00:00] : [Report Period].[Report Periods].[Quarter].&[2014-06-01T00:00:00]}
on 0,
non empty
filter(
bud, [Measures].[Qty Sold] <> 0 OR [Measures].[QTY Service Calls] <> 0)
on 1
from [db1]

MDX results to Excel in Power Pivot

1 Answers