BPC MS 7.5 MDX query return all nulls

[Former Member]

Hi Everyone,

I am fairly confident with BPC, however I am fairly new to the analysis server side of things. I am trying to do a simple MDX query on a cube however it always returns NULL results. Also when explore the cube through the browser I get blanks for the result.

I have tried quite a few things

-Processed the application set

-Optimised the application set

-Manual Processed the cube and partition

-Changed the security

-Changed the aggregation method and designs

All with no luck. I know that there is data in the corresponding fact tables and when processing the cubes its says there is 63 K of rows read. I have also checked the measures and ensured the aggregation type is SUM.

I hope someone can help

Cheers

Cameron

[Former Member]

Hi Patrick,

Yes its standard for SSAS cubes to do aggregation with out all dimensions, this is why I was thrown off by the BPC cube. The property IsAggregate was set to false for the last association in the association chain for a dimension. the default for cube is true. Turning all the dimensions to IsAggregate=True sets up proper aggregation.

Thanks again for your help,

Cheers

Cameron

[PatrickFavre]

Hi,

Thanks for this detail. Nice to know that...

This concludes this thread. I think you can close it.

Kind Regards,

Patrick

[PatrickFavre]

Hi Cameron,

Not sure to really understand your point, but from where are you browsing the cube in SSAS?

Do you use the "Browse" link, when right-clicking on a specific one?

If yes, here you have to drag&drop particular dimensions either in rows, columns (for example account and time), and also use the section above the result grid to filter any other dimensions (for example category, rptcurrency, entity, ....)

After defining every dimensions, then, drag&drop "Measures" in the data section, and you then should be able to see some aggregated data in the result table.

Hope it will help.

Kind Regards,

Patrick

[Former Member]

Thanks Patrick,

But that is the problem, it does seem to be aggregating (I have checked the aggregation type, it is set to SUM). When I do what you explain above I get blanks, however when I do a pivot on the underlying fact tables there is lots of data. When I process the cube, it indicates that 63 K of rows are read in from the fact table.

Its seems to only be the BPC models, all the other cubes I have on the server work as expected. I have tested the cube on 3 different servers and still have the same problem. I have also tested the appshell just to be sure and have the same problem.

Cheers

Cameron

[PatrickFavre]

Hi again,

Performing the steps in the same order than I mentioned above should display some data in the Browse window of your cube. So it's a little bit strange. Are you just sure you have drag&drop ALL dimensions either in row, columns or in the filtering section?

Which version of SQL server are you running? And also which Service Pack and which cumulative update package?

Best Regards,

Patrick

[Former Member]

Thanks again, sadly it didn't work.

I am curious to know why only bring over all dimension would work, when I have other cubes (e.g AdventureWorks) that I can bring just the measure over and get a value.

[Former Member]

So I have got it working, by restoring an older version of the database and then exploring the data with extra dimensions in the filter section. I am still confused why I can't just drag a measure over and get a number. Other cube work this way.

[PatrickFavre]

Hi,

I don't really know what's the behavior with other native SSAS cubes, but for BPC cubes in SSAS, I was always able to retrieve data only by filling-in the filtering section and having ALL my dimension being referenced.

Hope this helps.

Kind Regards,

Patrick