Force mdx query to return column names

2
votes

When connecting from powerpivot to SSAS, I got the following problem - if for some reason no rows are returned by mdx query, no column names are returned either and powerpivot gives an error. When executing such a query in SSMS I got 0 rows if mdx returns nothing and 2 rows if mdx returns 1 row (column names and the row itself). So - can I somehow force SSAS to return column names? Query currently looks a bit like this:

SELECT NON EMPTY {[Measures].[Measure1]} ON COLUMNS,
NON EMPTY {Filter([DimLocalDate].[Date].&[20110101]:[DimLocalDate].[Date].&[20120101],
[JobStatus].[JobStatus].&[1] } ON ROWS
FROM [TheCube]

In my app customer can specify dates and status value. If no results are found I would expect an empty result set, but instead I got an error. Thanks.

2
ssasmdxpowerpivot

2 Answers

3
votes

re SSIS error: [MEMBER_CAPTION] cannot be found at the datasource.

Thanks your solution helped me. I had the same issue using SSIS, SSAS to output a flat file report

i'll post an extended example to help others define the dummy set. Please note that the date is inserted into MDX script dynamically.

SSIS throws an exception when no data exists for the date, and therefore no results are returned. This messes up the column order

--create a blank set so that data is still returned when there are no results for that date
set [BlankRowSet] as 
([Activity Period].[Days].[Day].&[2014-02-02T00:00:00], [Well].[Location].[Location].&[])

--create the active set as the crossjoin between Days, Wells
set [ActiveSet] as 
nonempty([Activity Period].[Days].[Day].members * [Well].[Location].[Location].members )

SET [RealSet] as IIF(COUNT([ActiveSet]) > 0, [ActiveSet], {[BlankRowSet]}) 


select {[Measures].[Total boe]} ON COLUMNS 
,[RealSet] ON ROWS 
FROM 
(select {[Activity Period].[Days].[Day].&[2014-02-02T00:00:00]:[Activity Period].[Days].[Day].&    [2014-02-02T00:00:00]} on 0 from [Volumes] )

where 
[Scenario].[All Scenarios].[Category].&[PVR Sales Estimates]
1
votes

I will check before the select if your NonEmpty clause will return a void set, and in that case put on row a dummy set that will be return to powerpivot at least 1 row. Something like:

WITH 
SET [mySet] as NON EMPTY ({Filter([DimLocalDate].[Date].&[20110101]:[DimLocalDate].[Date].&[20120101], [JobStatus].[JobStatus].&[1] })
SET [myRealSet] as IIF(COUNT([mySet]) > 0, [mySet], {[DummySet]}) 
SELECT NON EMPTY {[Measures].[Measure1]} ON COLUMNS,
[myRealSet] ON ROWS
FROM [TheCube]

would help?