CODEWITHSUNDEEP
ssasmdxcubecross-joinmicrostrategy
Niel
Niel

Reputation: 41

SSAS Cube design - MDX very slow

I'm busy tearing my hair out with this one. I have a cube with 1 fact and around 9 dimensions. The fact table only has around 120k rows in it so it is not a big DB by any means.

The issue is that with the reporting software I'm using (Microstrategy), the MDX it generates grabs all the data and uses that as a data source (instead of generating the MDX as you manipulate the reports). Either way, when adding a couple of fields the query quickly becomes unusable, and actually runs out of memory. I suspect it has something to do with my cube design.

For one, it does a cross-join on all the attributes, even if they're on the same dimension, as if it doesn't realize that there is a relationship between them.

I have played around with attribute relationships in BIDS (making the relationships rigid where possible) but that doesn't seem to make any difference.

The MDX generated is shown below. I don't have the option of customizing the MDX but I'm sure I'm not telling it what the relationships are correctly.

I have tried posting on the Microstrategy support site without too much joy.

Any assistance will be greatly appreciated.

Thanks

WITH SET AS '{[Dim Date].[Date].[Date].AllMembers}'
...
(all my dimensions appear here)
...
select {[Measures].[Total Purchases], [Measures].[New Cards Issued], [Measures].[New     Applications Received], [Measures].[New Applications Declined], [Measures].[New     Applications Approved], [Measures].[New Accounts Activated]} on columns,
non empty CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(C  ROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN(CROSSJOIN([dim0_select_members], [dim1_select_members]), [dim2_select_members]), [dim3_select_members]), [dim4_select_members]), [dim5_select_members]), [dim6_select_members]), [dim7_select_members]), [dim8_select_members]), [dim9_select_members]), [dim10_select_members]), [dim11_select_members]), [dim12_select_members]), [dim13_select_members]), [dim14_select_members]), [dim15_select_members]), [dim16_select_members]), [dim17_select_members]) on rows
from [Cards_Transactions]

Upvotes: 2

Views: 1116

Answers (1)

Eyal Ben Yehuda
Eyal Ben Yehuda

Reputation: 219

it will be good if you can elaborate on your needs.

is it for end users or developers? Do you want to allow users to perform self-service on the mdx cube? Does the data they are query needs to be live (updated to now) or it can be updated till the previous hour\day...?

there are 2 ways to connect to MDX sources in MSTR.

one way is - Direct Connection trough the Import Data interface.

with this way - mstr generates mdx queries according to your selections on-the-fly. that is why it is important to use some filters before dragging some attributes to the screen in Visual insight.

the second option is to connect MSTR to the MDX cube at the schema level. it reads the cube definition and creates attributes and metrics as they exists in your cube. it also allows you to map MDX attribute to a project Attribute in order to enforce security filters. this way you can build regular reports (you can also use prompts and filters...) and you can build i-cubes from the mdx cube.

you might find the second option more suitable for your needs.

since your MDX cube is very small i would suggest you to create an i-cube + incremental refresh report to update it (for example - update every 5 minutes with today's data).

Upvotes: 2

Related Questions