Sunday, February 13, 2011

OBIEE Error 39008

OBIEE Error 39008: Logical dimension table X has a source that does not join to any fact source.

I am using Oracle Business Intelligence 11.1.1.3.0 version to create a new repository. My usual procedure is to create a repository in iterative manner, developing a simple repository using some of the tables and finish a simple model and repository without adding too much of details. I keep adding more details with each subsequent iteration.  

I just finished my first iteration of repository development and it looks like this




















The consistency checker gives no error or warnings.










In my second iteration I created a dimension/hierarchy.























I now did a consistency check of my repository and got 12 warnings.






















OBIEE Error 39008: Logical dimension table X has a source that does not join to any fact source.

Now there are three things to check to check for 39008 Error/Warning:
1.     Make sure that there is a physical/foreign key join between the LTSs.
2.     Make sure that there is a complex join between the logical tables.
3.     Make sure that the granularity between the dimension and the fact is same. (Check content tab in LTS)

Everything seems to be in place in my repository. Here is the trick that solved my problem and remove all consistency warnings.

Go to BMM layer and expand your fact table and it’s LTS.



















Now, double click on the LTS to view properties and go to content tab.


Now, here is the trick. In the drop down box for 'Aggregation content, group by' select 'Column' instead of 'Logical Level'































Click OK.

Now do a consistency check. In case you don’t know the shortcut, you can press Ctrl+k to do a consistency check.

Tadaa….










All the 39008 warnings are gone.

Now go back to LTS of your fact table in BMM layer and check that the 'Aggregation content, group by' setting has reverted back to 'Logical Level'.

This is a documented procedure by Oracle. More information on OBIEE 39008 Warning:

Cheers!
Deepak

17 comments:

  1. Great Stuff. Deepak. I faced same issue and resolved the way you explained.

    Which is the link where we get all these documented bugs/feature (!) for OBIEE11g from Oracle?

    Thanks
    Hegde

    ReplyDelete
  2. Hi Hegde,
    Good to know that it worked out well for your scenario as well. I usually keep track of documented bugs/features at Oracle Support.

    Some good places that I go to learn and follow are-
    http://www.rittmanmead.com/category/oracle-bi-suite-ee/
    http://gerardnico.com/wiki/
    http://oraclebizint.wordpress.com/
    http://www.oraclenerd.com/
    http://rnm1978.wordpress.com/
    http://obiee101.blogspot.com/
    http://108obiee.blogspot.com/

    Cheers!
    Deepak

    ReplyDelete
  3. Hi Deepak

    I am MaurĂ­cio OBIEE specialist from Brazil and I like so much of this blog.
    I write about obiee too, but in portuguese to the people here found fast some subjects.

    I will put your address in my blogroll session, ok?

    Thank you,
    Mauricio

    ReplyDelete
  4. Hi Mauricio,

    I am glad that you are finding my blog helpful. Please feel free to add my blog address to your blogroll. Do share your blog as well.

    Thanks,
    Deepak

    ReplyDelete
  5. Hi Deepak,
    I created a time dimension with year-quarter-month-week-details as levels with year number , quarter number, month number, week number, date key as logical key correspondingly..when I click on estimate levels it is throwing the below error :


    PRES_GBL_DATE_DIM_TSTDim
    "CARE"."PRES_GBL_DATE_DIM_TSTDim"."Year" = 100
    "CARE"."PRES_GBL_DATE_DIM_TSTDim"."PRES_GBL_DATE_DIM_TST Detail" = 36525


    ERRORS:
    "CARE"."PRES_GBL_DATE_DIM_TSTDim"."Quarter"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "CARE"."PRES_GBL_DATE_DIM_TSTDim"."Month"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "CARE"."PRES_GBL_DATE_DIM_TSTDim"."Week"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.

    Do you have any idea about this ?

    -Siri

    ReplyDelete
  6. Thanks for your information. We faced the same issue and corrected it with your details.

    ReplyDelete
  7. Hai Siri,

    You have to give Number of Estimate levels to the child more than parent.because parent is always 1 and a parent can have n no of childs.
    Try this one.

    Thanks&Regards,
    Chaitanya.P

    ReplyDelete
  8. Hai Deepak,

    Thanks for the wonderful work around provided. You solved my trouble with in no time.

    Appreciate your help!

    Gireesh

    ReplyDelete
  9. YOU ROCK!!! I was looking everywhere !! Thank you so much for helping all of us out.

    ReplyDelete
  10. Hello,

    I tried your method, however, I am still facing the error 39008. I have 2 database. From one database, I have a Table A, Table B (Fact Table) and from another database I have Table C. The error 39008 is shown for Table C. Could you please help me out here...

    Thanks

    ReplyDelete
  11. Glad it helped you guys!

    Cheers!
    Deepak

    ReplyDelete
  12. Great Solution. Thanks brother.

    ReplyDelete
  13. Clear and concise; sign of a good communicator. Worked a treat! Thanks a bunch dude!

    ReplyDelete
  14. Hi Deepak
    I created Time Dimension in OBIEE11g , After estinmate level I am facing this error. Can you help me

    TimesDim
    "SH_Business"."TimesDim"."Times Detail" = 0


    ERRORS:
    "SH_Business"."TimesDim"."CalYear"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalQuarter"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalMonth"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalWeek"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.

    ReplyDelete
  15. Hi Deepak
    I created Time Dimension in OBIEE11g , After estinmate level I am facing this error. Can you help me

    TimesDim
    "SH_Business"."TimesDim"."Times Detail" = 0


    ERRORS:
    "SH_Business"."TimesDim"."CalYear"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalQuarter"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalMonth"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.
    "SH_Business"."TimesDim"."CalWeek"<-- Failed consistency check, level count does not match with parent levels. Please check level attributes.

    ReplyDelete
  16. Hi Ajit,

    Try to manually add levels e.g. Year -1, Qtr -4, Month -12, Week -52. The important part is to keep the ratios of levels consistent.

    Thanks,
    Deepak

    ReplyDelete