This discussion is archived
6 Replies Latest reply: Sep 2, 2013 2:21 PM by rcortez RSS

Multiple Facts at the same report

rcortez Newbie
Currently Being Moderated

Hello gurus,

 

How can I use multiple facts at the same report using OBIEE 10g?

 

I followed the instructions of this website but it's not working...

http://obibb.wordpress.com/2010/05/31/multiple-fact-reporting-on-non-conforming-dimensions/

 

 

Physical Model:

 

ASSET (DIMENSION)  ------------ ASSET (FACT) ----------------- PERSON (DIMENSION) ----------------- CAMPAIGN (FACT) ---------------- CAMPAIGN (DIMENSION)

 

Business Model:

 

ASSET (DIM) ---------------- FACT ------------------- CAMPAIGN (DIM)

                           (ASSET - CAMPAIGN)

                                        |

                                        |

                                        |

                                        |

                              PERSON (DIM)

 

 

Asset and Campaign: Non conformed Dimensions;

Person: Conformed Dimension;

 

Note that I want to use at my report some asset and campaign informations to answer questions like:

Wich assets were created after the campaign launch? or List of asset by person and campaigns.


Is it possible to do? I'm trying to change all kinds of column levels (Total/Detail) and it keeps sending an error like:
"Internal error: No matching table for expr Fact_W_CAMP_HIST_F_Teste.SOURCE_WID, File server/Query/Optimizer/SmartScheduler/PhysicalRequestGenerator/Src/SQOSPSimplifyJoin.cpp, line 118."

 


Thank you in advance.
Ramon Cortez

  • 1. Re: Multiple Facts at the same report
    Srini VEERAVALLI Guru
    Currently Being Moderated

    Table W_CAMP_HIST_F might got content tab setting for which there is no relation ex: Asset


    if helps mark

    http://cool-bi.com

  • 2. Re: Multiple Facts at the same report
    rcortez Newbie
    Currently Being Moderated

    Hello,

     

    Thank you for your help.

     

    Now I'm getting this error: "None of the fact tables are compatible with the query request Fact.SOURCE_WID". ---> Fact.CAMPAIGN_WID


    I'm actually not sure about the behaviour of the content tab. As I understood, I must use Total for non-conformed dimensions and detail for conformed, right?

     

    In this case I have:

     

    ASSET (DIMENSION)  ------------ ASSET (FACT) ----------------- PERSON (DIMENSION) ----------------- CAMPAIGN (FACT) ---------------- CAMPAIGN (DIMENSION)

     

     

    So, at Asset (FACT) content tab I have to set detail for Asset (DIM) and Person (DIM) and total for Campaign (DIM).

    At Campaign (FACT) content tab I have to set detail for Campaign (DIM) and Person (DIM) and total for Asset (DIM).

     

    But at the columns, if I set detail for Campaign(DIM) as total, all the other columns will show only Campaign at content tab. Why is it happening?

     

    My requeriment measure is like:

     

    SUM(CASE WHEN Asset.Date1 BETWEEN Campaign.DateA and Campaign.DateB AND Asset.Product = Campaign.Product and Asset.Status  = 'Active' THEN 1 ELSE 0 END)

     

    How can I do this using person as conformed dimension?

     

    Thanks,
    Ramon Cortez

  • 3. Re: Multiple Facts at the same report
    Srini VEERAVALLI Guru
    Currently Being Moderated

    Try to go for only 1 fact ie Asset

    then Asset properties Add or Map or + sign and then add Person and then Comaign

  • 4. Re: Multiple Facts at the same report
    rcortez Newbie
    Currently Being Moderated

    Do you mean add at my Asset fact source a Map to the Non-conformed dimension (Campaign)?
    Because if you do, what would be the join to this fact? As I know, you can only add conformed dimensions, right?

     

    Thanks,
    Ramon Cortez

  • 5. Re: Multiple Facts at the same report
    Srini VEERAVALLI Guru
    Currently Being Moderated

    I was referring these tables ASSET (FACT) ----------------- PERSON (DIMENSION) ----------------- CAMPAIGN(FACT)

    i'm not sure what you got?

  • 6. Re: Multiple Facts at the same report
    rcortez Newbie
    Currently Being Moderated

    Hello,

     


    Thanks for the tips. It seems that you're right about the mapping the other fact.
    I'll do some tests and mark your answer as correct ASAP.

     


    I'm still having some issues do to a measure at my Campaign Fact that uses Asset Dimension columns.

     

     

    Ex:
    Mapping the columns from Campaign Dimension and Asset Dimension as logical columns at my Fact (each one must be mapped at it's own facts) and creating a measure that uses those columns I get the following error:

     

    [nQSError: 14020] None of the fact tables are compatible with the query request <Catalog_name>.<Measure_name>. (HY000)

     

    I'll add as source at Campaign Fact the Asset Fact and also Asset Dimension and see if changes the behaviour of the query using the physical columns instead.

     


    Thanks,
    Ramon Cortez

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points