Data module and excel upload join - performance

[cognosjon]

hi everyone,

quick question as I think I'm struggling to get my head around it..

I have an existing FM package (star schema)

I then have an uploaded excel file with two columns. (ID, GroupDescrip)

The excel file is being joined to one of the dimension tables in the package on 1-1 relationship using the ID. So in a way snowflaking this dimension out a little.

When the module is then used for reporting it generates the correct answer in that it pulls back the additional group description, but its horrendously slow.

Its an end user solution so they have no means of seeing keys in the fact tables so they cant create a merge table (merged query subject) and join that to the fact instead but the performance is causing issues.

Would anyone have any suggestions as to a way around this in the front end?

Appreciate any suggestions as to where the problem may exist.

Thanks

[MFGF]

hi everyone,

quick question as I think I'm struggling to get my head around it..

I have an existing FM package (star schema)

I then have an uploaded excel file with two columns. (ID, GroupDescrip)

The excel file is being joined to one of the dimension tables in the package on 1-1 relationship using the ID. So in a way snowflaking this dimension out a little.

When the module is then used for reporting it generates the correct answer in that it pulls back the additional group description, but its horrendously slow.

Its an end user solution so they have no means of seeing keys in the fact tables so they cant create a merge table (merged query subject) and join that to the fact instead but the performance is causing issues.

Would anyone have any suggestions as to a way around this in the front end?

Appreciate any suggestions as to where the problem may exist.

Thanks

Hi Jon,

A couple of questions:
1. Is the package published using DQM?
2. Is the uploaded file sorted in order of the ID key?
3. Are you using CA 11.0.x or 11.1.x (sorry, I lied, that's three questions) :)

Cheers!

MF.

[cognosjon]

Hi MF,

Sorry should have put that basic information in to begin with.

We are running 11.1.7 fp1
Package is DQM

Sorting??? I'll have to have a check when I get back to my machine... I'll have a look and update.

Thanks

[cognosjon]

ok,
so yes the uploaded excel file has been sorted, though I note that when you save the module and then go back into the module editor that the sort definition on the uploaded file is no longer applied.

so, to answer your questions again MF

Is the package published using DQM? Yes DQM

2. Is the uploaded file sorted in order of the ID key? Yes ( but not sure its retaining the sort order and its not apparent from the generated SQL in the report)

3. Are you using CA 11.0.x or 11.1.x (sorry, I lied, that's three questions) we are on 11.1.7 fp1


Thanks

[MFGF]

ok,
so yes the uploaded excel file has been sorted, though I note that when you save the module and then go back into the module editor that the sort definition on the uploaded file is no longer applied.

so, to answer your questions again MF

Is the package published using DQM? Yes DQM

2. Is the uploaded file sorted in order of the ID key? Yes ( but not sure its retaining the sort order and its not apparent from the generated SQL in the report)

3. Are you using CA 11.0.x or 11.1.x (sorry, I lied, that's three questions) we are on 11.1.7 fp1


Thanks

Thanks Jon. As you're using 11.1.x that means you have the performance boosts IBM added for the parquet data. Having the data pre-sorted is also a technique for improving the performance of things like filtering (and I assume joins).

It would be interesting to know if the reports are joining from the saved data set to the package data or from the package data to the saved data set. I'm guessing DQA would give you some clues.

Cheers!

MF.

[cognosjon]

Good suggestion,

I'll take a look.

thanks

[MFGF]

Good suggestion,

I'll take a look.

thanks

I'd also be interested to see what performance is like if you load the required data from the package into a Data Set and join this to your uploaded data in a Data Module? Is it just as bad?

Cheers!

MF.

[cognosjon]

update....

So i'm struggling at the moment to get DQA talking to my server (long story) but i've had some success with an old school approach.

Ive  created a merged query subject (create table view in data modules) between the uploaded file and the dimension from the package. Then joined this to back to the fact table. 

As expected this works and is nice and speedy in its response and will provide the user with the functionality they need for the work they are conducting this week.

I will keep looking into the problem though as I'm intrigued as to what's going on.

I'll update further once I get any more info.

Thanks