Error 38030 and Error 15003: Creating Multiple Hierarchies in Same Dimension

JaredB-Oracle

I am new to OBIEE 12c and building RPDs in the Administration Tools. I built a star-schema, and many of my dimension tables contain foreign keys from other dimension tables. For example, I have an OrderList table and inside this table, I have a PortCarrier_ID, which refers to the PortCarrier table. I can then get the Carrier and Service Level associated with this Order ID. I can also get the Origin Port and Destination Port from the Port Carrier Table. The PortCarrier table has a Dest_Port_Location_ID and Orig_Port_Location_ID. These two IDs refer to other attribute tables, Orig_Port_Locations and Dest_Port_Locations, respectively. I think you get the idea. I have all of my data and SQL and pictures in a GitHub Repo, if you wish to refer to that:

https://github.com/jaredbach/LogisticsDataset

Here is a picture of my schema (it is also in the GitHub repository but putting it here for ease)

This is what it looks like in the Oracle BI Administration Tool (RPD builder).

When I run the consistency check, everything is all good, but I am trying to build hierarchies, so that I can correctly drill down on all of my data. Everything kinda feeds back to the FreightRates, OrderList, and PlantConstraints, so I want to drill down on all of these. These are what the hierarchies look like.

When I try to do the level based hierarchies, however, I receive the following error when I drag the BMM layer to the Presentation layer.

Does anyone know how I can fix this, so I can correctly drill down on everything? When I tried to just create a single hierarchy with the OrderList table, I got this error.

When I made a single hierarchy with just FreightRates, I got this error.

When I made just a single hieracy for FrieghtRates, there was no error. I think I need to combine all of the hierarchies somehow but am not sure how to do that. Any input would be helpful. Thanks so much, in advance.

Gianni Ceresa

If your second picture is the business model diagram, that's by far not a star schema.

The business model should contain 1 (or more) "simple" star schemas: 1 logical fact table connected to 1 or many logical dimensions. Your picture shows dims connected to dims etc. Not what the RPD expect at all.

In case that picture is your business model, you should review the meaning of physical layer vs BMM layer: in the physical layer you can have whatever form of data you need (dimensional model, snowflake, transactional 3NF or whatever), but then in the BMM layer you forget any physical representation of things (the fact a dim contains FK to another dim table etc.), and model a simple start schema.

You can have, if needed, multiple stars in the same business model and conformed dimensions when possible/needed.

JaredB-Oracle

How would you go about converting what I have into a star schema? I think I am a bit confused on how to do that. I was under the impression that you could have dimensions connected to dimension tables, so long that it is a foreign key connecting the two and there are not repeating columns (which is the case for me). If I don't do the hierarchies, I get no errors. So I am under the impression that my schema is not the issue per say. I am just not sure how to build the hierarchies.

Gianni Ceresa

The RPD has tons of rules, and hierarchies enforce many of them. Not having any warning or message when not doing hierarchies doesn't mean it will always work as expected, the RPD is just skipping various checks.

In the business model you can "reduce" (melt?) snowflakes into a single logical dimension by adding various tables into the LTS of your dimension. This tells the system that behind the single logical table you have a set of tables with the joins as defined in the physical layer. But from a business point of view it's only a single dimension.

JaredB-Oracle

Hmmm, OK. I will try to do this. I am not exactly sure how to melt the snowflake but will try to figure that out.

Gianni Ceresa

"Melting" the snowflake is how I call it because it's the easier word I remember. Not sure of the official wording for this step. There are various blog posts around covering this point, maybe try to search for snowflake in the RPD (and because now there is a DB company called Snowflake you will need to filter your results a bit).

The point is really to use the power of the logical table source(s) in your logical table.

There are many things that can be done with a LTS and one is to actually say that this unique LTS actually represent "Table1 inner join Table2 left outer join Table3 right outer join Table 4". And the logical table will have access to all the columns represented by that join logic instead of just the columns of a single physical table.

It's really one of the key feature of OBIEE and how you have to forget the physical representation of data when you build your business model.

JaredB-Oracle

That totally makes sense. I will do research and try this and report back to the forum if I have questions and/or if I figure this out!

JaredB-Oracle

The solution was to create logical tables that joined together my physical tables (AKA melting the snowflake). Thanks so much for the help.

JaredB-Oracle

RPD-Building.pdf

Here is how I built my RPD.