BEx Query on SPO vs Multiprovider

[Former Member]

Hi,

I have a SPO which partitions data via Calendar Year. The plan is as time goes by i will add extra partitions for the other years and the process chain which is automatically created I would amend to not include years that data would no longer be created for.

Question i have is should i be creating the BEx Query on a multiprovider which uses the generated partitions OR should i be creating the BEx query on the SPO?

At the moment I am only using the SPO as a way to automatically create the partitions.

Cheers,

[sander_vanwilligen · Accepted solution]

Hi Leo,

I advice to use a MultiProvider and directly assign the SPO as the Infoprovider, i.e. instead of the individual PartProviders. This way new partitions / PartProviders are automatically taken into account w/o the need to add them on a yearly basis. Also the MultiProvider hint mechanism works in conjunction with the SPO.

Best regards,

Sander

[Former Member · Accepted solution]

Hi,

Sure, thanks.

My only concern is the number of InfoProviders in the SPO will expand over time and in turn the multiprovider would have a significant number if InfoProviders under it (which are in the SPO). If I add the individual InfoProviders in the Multiprovider I can control the number in there.

But I guess the question is if I use the "multiProvider hint" mechanism does it matter how many infoproviders are in the multprovider as only the infoproviders needed will be used?

Cheers

[Loed · Accepted solution]

Hi Leo,

Several cubes (each cube consists a year of data) under a multiprovider is better compared to a multiprovider having only one (1) cube - perfomance wise..

Regards,

Loed

[Former Member · Accepted solution]

Hi,

Sure, just clarify my concern/question is if there is 20 InfoProviders in the SPO (20 years worth of data).

I have two options

1. use the multiprovider to select which number years I want to be included for reporting as i can add/remove cubes. Password hint will be implemented. I can included the 10 years worth of cubes i need.

2. use the SPO in the multiprovider and the password hint is automatically implemented. All 20 cubes are included.

If option 2 is selected the advantage is that the cubes are included in the final reporting as needed bsed on the hint. Is performance impacted becuase there are 20 cubes? Or does that not matter as the password hint is there?

Cheers

[Loed · Accepted solution]

Hi Leo,

What do you mean by password hint?

So you want to compare the performance of multiprovider with 20 cubes vs multiprovider with SPO (divided into 20 for 20 years)?

Performance is still better mate in the 1st option..

Regards,

Loed

[Former Member · Accepted solution]

Hi,

Sorry I meant "multprovider hint" - effectively the infoproviders needed based on the restriction in the Bex Query are used.

So you want to compare the performance of multiprovider with 20 cubes vs multiprovider with SPO (divided into 20 for 20 years)?

Actually want to compare:

- multiprovider with 10 cubes (as only 10 selected from InfoProviders created by SPO)

- multiprovider with SPO (divided into 20 for 20 years)?

Both scenarios use the multiprovider hint.

Cheers

[sander_vanwilligen · Accepted solution]

Hi Leo,

Exactly, you got the point. The Multiprovider Hint is taking care of "logical partition pruning" as long as you select the appropriate SPO Partitioning Characteristic, i.e. Calendar Year of Fiscal Year in your case. E.g. if you filter on year 2015 in your BEx Query, the OLAP processor will only query on PartProvider 2015 and discard all other PartProviders.

That means that you can scale out with new partitions in the future w/o an impact on query performance.

Best regards,

Sander

[Loed · Accepted solution]

Hi Leo,

Did Sander already answer your query?

Regards,

Loed

[former_member202718]

Hi Leo,

Create SPOs and map them to MP and do reporting on MP....The Flexibility is very high in case of MP reporting..Also the existing structure remains intact.

rgds

SVU

[Loed]

Hi Leo,

It is still better to create the query in MULTIPROVIDER so that if ever you changed the structure of your SPO, you are not going to recreate the queries you have created already..I think this is still the "best practice"..

Regards,

Loed

[Former Member]

Thats also my experience.

But watch out, I've heared that there are problem with SPO by migration to Hana