SAP Universe design approach

[horynao]

Hello everyone,

I would like to ask about universe design. I like to know some approach, that in my case there are two fact tables and one dimension table. And I would like to let an user to choose period format from table 1 (etc. 2023-01, 2023/01). But there is an issue in database explain plan. If an user select table 2 and table 3 columns and he does not choose period_key from table 1 specifically, the optimizer goes through all rows in table 3. If an user choose period_key, partitioning is applied corectly.

I have following data layer. Table 3 has cca 8 mil. rows.

How can I say to the universe to select data from each table and the partitioning is applied and an user could set only one condition?

Thank you in advance

Best regards

Ondrej

[amitrathi239 · Accepted solution]

Yes, above picture is correct.Once you set the two context then users can drag objects from any table.if users will drag objects from Table2 and table3 in the single query then BO will handle itself (you will see two sql in the webi query sql panel) and accordingly generate the two sql (one for each context ).Thats the purpose of setting up the context.

[horynao · Accepted solution]

Thank you,

Best regards

Ondrej

[amitrathi239]

I hope you have only created the period name/quarter/year objects from Table 1 only in the universe.To handle the table 2/table3 (Fact tables), you need to create two context.You will always get the wrong result if table 2/table 3 appear in the single sql(Check webi query sql).Context will force to run as two separate sql's in the webi query background.

Table1 and Table2

Table1 and Table3

Below are the some blogs to understand Universe context

https://michaelwelter.wordpress.com/2011/06/29/universe-contexts-in-a-nutshell/

https://evtechnologies.com/simplify-contexts-inside-of-sap-businessobjects-universes-with-the-idt/

[horynao]

Hello Amit,

thank you very much. So do you mean like this?

Table 1 is physical table and various format types are at this table yet.

And one more question, so I have to say to my users, that they can not combine columns from this two fact tables in one query? And If they need to show measures from each fact table do merge at report level? Even if joins are set up properly and universe return correct values (until now there are contexts what returns correct sums of measures)?

Thanks,

Ondrej