How can we let OBI EE ignore an Implicit Fact Table Column when creating a column based prompt limit

Manoj Dixit

Hi,

I have a subject area containing several fact tables. An implicit fact table column has already been specified. This column is based on 'select 1 Dummy from dual' select table type in physical layer.

I want to add a new prompt to an existing Dashboard Prompt. This prompt should be limited to All Prompts. The issue is since there is already an implicit fact table the LOVs are nothing but a Cartesian product (all rows from all dimensions available on the Dashboard prompt).

Is there any way to specify to ignore the implicit fact table and actually use the correct fact table to display the LOVs for the new prompt? Or how could the new prompt be designed?

If the suggestion is to remove the Implicit Fact table from the Subject Area, how to do an impact analysis of this change?

OBI EE version is 11.1.1.7.0.

Any questions, please let me know.

Thank you.

Regards,

Manoj.

[Deleted User]

Manoj Dixit wrote:Is there any way to specify to ignore the implicit fact table and actually use the correct fact table to display the LOVs for the new prompt?

Since someone built your system like this, your column forcing a cartesian product IS actually "correct". I.e. it works as your implementation team designed it.

You could force your prompt over a different fact table by either changing the logical SQL of the first column for example but still I'd question a fake implicit fact column which forces cartesian products in any case...

Manoj Dixit

Hello Christian,

Thanks for your reply.

I am thinking of exposing dwh key of the new prompt's dimension in the presentation layer and use it in a logical SQL to build the new prompt. The idea is to run the Analysis based on the other prompt values and create the LOVs for the new prompt. Then let the users select particular values from the LOVs. This new prompt won't have Apply button. The interface is little complex so may not be rolled out.

The logical SQL will be something like:

Select person _names from sales_staff where dwh_key in (select sales_person_key from sales_fact where day_name in (selected _day_values) and {other dimension filter conditions}).

Regards,

Manoj.

Manoj Dixit

Hi,

The above approach is definitely not an easy one.

By the way, should we avoid specifying implicit fact table then? I am not sure about its use now. And if we were to remove it, how can undertake doing an impact analysis? Could you share a few tips please?

Thank you.

Regards,

Manoj.

[Deleted User]

Manoj Dixit wrote:By the way, should we avoid specifying implicit fact table then? I am not sure about its use now. And if we were to remove it, how can undertake doing an impact analysis? Could you share a few tips please?

No you shouldn't. You should design our Subject Areas and Business Models in a fashion which reflects requirements. Dropping implicit fact columns or keeping them must never be a random choice but driven by needs and design.

Thomas Dodds

If your subject area has multiple fact tables from your logical model, the implicit fact drives the relating of dimensional attributes.

General DW principles are that EVERY fact has a dimensional context and dimensional attributes are related through the facts.  Basically the 'idea' behind the star schema and the avoidance of circular joins.

So ... if you have a SalesPerson dim ... you might want to see sales person activity by day and not show a fact ... so you probably set up the implicit fact as being sales (days are shown for sales persons with sales activity).  But you might have a subject area that maybe you want to associate the sales person and the date through inventory (why? who knows!)  - the implicit fact only gets 'triggered' when no fact join is included in the query (it's all dimensional attributes)

"This column is based on 'select 1 Dummy from dual' select table type in physical layer." -- is questionable.  I suspect someone had the idea that you MUST have an implicit fact, but really you SHOULD have one per subject area -- where the subject area is designed for a specific business functional area and thus the implicit fact actually makes sense.  Comes down to planning and knowing the requirements.

[Deleted User]

Yeah as I said: I'd question a fake implicit fact column which forces cartesian products in any case. Same fake "I don't know what I'm actually doing" rubbish as having 1=1 joins to "even out facts".

Manoj Dixit

A PoC based on the alternative approach - using a Logical SQL - seems to be working.