OBIEE RPD Design: Convert Snowflake to Star schema from multiple sources in (Combine dimensions)

As I play more with OBIEE, I learn more about what it is capable of and where its main power resides. OBIEE has 3 layers: Physical, Business Model and Mapping, and Presentation. The middle layer, BMM, is what makes OBIEE special: it is where we can define how data from different sources and tables come together and form a nice and clean star schema.

Like most of my posts, I am gonna explain how this is achievable by demonstrating a demo. I defined 2 databases in my local instance of SQL Server. One with 1 fact table and 3 dimensions and another one with a single dimension that will be referenced by one of the dimensions in the first database: DimCustomer, DimLocation, DimProduct and FactTest in database Test and DimProductCategory in database Test2.

Screen Shot 2015-02-10 at 9.56.51 am

Let’s go through the steps to create and design the RPD for our data source. Launch Oracle BI Administrator:

a) Create first database connection:

Click on File and then New Repository. This will launch Create New Repository wizard. As you know, we can define only one data source using Create Repository wizard. I used this wizard to create my first database connection to Test database. I didn’t list steps here because it is very easy and straightforward.

b) Create second database connection:

1- Right-click on Physical pane of Oracle BI Administration Tool and select “New Database..”

2-Provide a name in General tab. Click on “Connection Pools” tab and then click on the green “+” sign.

3- Give the new Connection Pool a name, select the data source pointing to the second database from “Data Source Name” drop-down list and enter appropriate “User Name” and “Password”, as shown below. You will be prompted to re-enter the password after clicking Ok.

Screen Shot 2015-02-10 at 10.38.51 am

4- Click Ok again in New database window.

5- Now that we have added a new database and connection pool to our Physical layer, it’s time to add tables to the new connection. Right click on the entry for the new connection pool ( in my case, Connection Pool 2) and select “Import Metadata”. This will launch Import Metadata wizard, where you can select objects that should be added to the Physical layer.

6- Select the type of objects you want to import and click Next. In my tutorial, I select DimProductCategory from database Test2 and add it to Repository View, and then click Finish:

Screen Shot 2015-02-10 at 11.21.11 am

Your Physical layer pane should have 2 entries for 2 database connections and tables imported on each connection. My Physical Layer looks like this:

Screen Shot 2015-02-10 at 11.33.17 am

C) Physical Diagram

Now it is the time to define how tables are joined together in Physical layer.

1- Select tables from both connections, right-click and select “Physical Diagram” and then “Selected Object(s) Only”.

I didn’t define any foreign keys in my database, therefore I’ll get none of the tables related together in my initial physical diagram and I have to define how tables are joined manually.

2- To define the first join, click on New Join button, click on the fact table and then on DimProduct. This will create a join between those 2 tables based on the columns with the same name. You can change this in the next window that opens, “Physical Foreign Key”:

Screen Shot 2015-02-10 at 11.56.36 am

3- Do the same for the rest of the joins, including DimProduct and DimProductCategory. Note that there is no difference between joining the tables on the same database and tables that are on different databases. My diagram looks like this after setting up the join between all tables:

Screen Shot 2015-02-10 at 12.21.40 pm

d) Business Model and Mapping

After finishing with physical layer, it is time to define the business logic in BMM layer. In this section, we are gonna create a new Business Model, add objects from Physical layer to it, and define our Star schema on top of snow-flake diagram we created in physical layer by combining DimCustomer & DimLocation into one dimension, and DimProduct & DimProductCategory into another.

1- Right-click in Business Model and Mapping pane and select “New Business Model..”. Give your model a name and un-check Disabled checkbox.

2- Drag and Drop your fact table (FactTest) into the new BMM model:

Screen Shot 2015-02-10 at 1.28.36 pm

3- Now it’s time to combine our first tables together and create a single dimension. Let’s start with DimProduct and DimProductCategory, the 2 tables that reside on separate databases. We will use “Logical Table” to do this. Right-click on Business Model, then “New Object” and then select “Logical Table…”. Logical Table window will open.

4- Give your new logical table a name in “General”. Let’s call it DimMasterProduct.

5- Click on “Sources” tab. As its name implies, this is where we can define the source(s) for our new logical table from tables available in Physical layer. Click on the green “+” button. Logical Table Source window opens.

6- Give it a name.

7- The next step is very important: click on green “+” button below Name textbox, to map it to a physical table. A new window open which lists all the tables used in our Physical diagram:

Screen Shot 2015-02-10 at 1.44.56 pm

Click on DimProduct and then click “Select” Button. You’ll see Logical Table Source window again, with DimProduct added as the source:

Screen Shot 2015-02-10 at 1.47.25 pm

8- Click on the “+” button again to add another table. You’ll see that instead of all tables in Physical layer, only those that are joined to DimProduct in Physical Diagram are listed here. Select DimProductCategory. You’ll see a new Join is defined and you can change it from inner join to right or left join:

Screen Shot 2015-02-10 at 1.51.04 pm

9- Click on “Column Mapping” tab. Here we can add the columns from our sources to our logical table. Click on “Add New Column” button. Logical column window opens.

10- Give your new column a name (Product_ID). Click on “Column Source” tab. Select (Click) the logical table source you created in previous step and click OK:

Screen Shot 2015-02-10 at 2.04.52 pm

A new Logical Column is added to the “Column Mapping” tab of “Logical Table Source”. Select the right column from Expression drop-down list:

Screen Shot 2015-02-10 at 2.09.01 pm

11- Repeat the steps to map the rest of columns for the logical table. I added 4 columns, as shown below:

Screen Shot 2015-02-10 at 2.11.17 pm

Click OK to go back to Logical Table window. Click OK to exit Logical Table window.

e) Business Model Diagram

Notice that both FactTest and DimMasterProduct have a # mark on them, implying they are Fact tables. OBIEE will treat all tables with no join pointing to them in BMM layer as fact tables. To change this, we need to define our “Business Model Diagram”.

1- Right-click on your Business Model and select “Business Model Diagram”, and then “Whole Diagram”. My 2 tables in BMM layer namely FactTest and DimMasterProduct are shown.

2- Like what we did for Physical Layer, join the 2 tables together by clicking on “New Join” button, and then FactTest and DimMasterProduct. Logical Join window opens:

Screen Shot 2015-02-10 at 2.29.55 pm

Notices the difference? Here, unlike Physical layer, we don’t need to define which columns will be used to join 2 tables together: OBIEE will work it out based the physical relationship of tables in physical layer.

My diagram looks like this now (Note there is no # on DimMasterProduct anymore):

Screen Shot 2015-02-10 at 2.48.01 pm

f) Presentation Layer

As you know most probably, you can add your BMM model into Presentation layer just by dragging it and dropping into Presentation layer:

Screen Shot 2015-02-10 at 2.54.09 pm

Now, save your solution and when asked if you want OBIEE to check Global Consistency, click Yes. You’ll notice global consistency failed with an error on the logical table not having a primary key defined for it:

Screen Shot 2015-02-10 at 2.56.50 pm

This error can be fixed very easily:

1- Double-click on your logical table’s entry in BMM layer.

2- Go to “Keys” tab. There should be an empty row added.

3- Enter a name for the Key in “Key Name” column.

4- Select the column that is the primary key of your table from “Columns” drop-down.

5- Select the key you just created from “Primary Key” drop-down.

Screen Shot 2015-02-10 at 3.00.38 pm

6- Click Ok and save your RPD. Click yes for OBIEE to check Global Consistency.

7- Notice the change in the icon of Business Model in BMM layer.

Now your RPD is ready to be deployed and used for analysis. This method applies to any other scenario where 2 or more tables need to be joined together to create a single dimension: In other words, to convert snow-flake to star schema.

Hope I didn’t confuse you with such a long post,


Search for Objects in an instance of SQL Server

There are cases when a developer needs to look for a specific object (table, Column, etc.) inside a database. A good example of such a scenario is when you have a database that has no diagrams or PK-FK constraints assigned, and you want to find out the PK-FK relationships and how tables in the database are connected together. This happens regularly in developing a Data Warehouse and designing an OLAP solution, when you want to transfer all the tables required to the Data Warehouse. And when the database is huge in terms of number of tables and views and the naming convention is not good and understandable, this becomes crazy!

SQL Server Management Studio (SSMS) gives you the ability to export the script used to do any operation on any type of object accessible through SSMS. For example, to get the T-SQL script for creating a table you can right-click on the table, select Script Table AS -> CREATE TO -> New Query Editor Window.

You may have guessed that we can follow almost the same procedure to export all the objects in a database to a query window and look for an object, e.g. a column, using the its name. To do this follow these steps:

1- After connecting to your instance of SQL Server, right-click on the database you are going to search in, navigate to Tasks, and then Generate Scripts… . The Script Wizard will launch:

2- Click Next in the Welcome window. You will see the Select Database window. This is where you can select the database you want to create the script for and search in:

3- In the next page, you can select the options for the objects you want to script. If you are going to get the script of the database just for searching an object in it, leave the options as they are:

4- Choose Object Types page is where you can select the types of objects to script. Note that you can select only the object types of which you have at least one instance in your database. For example, you won’t see the option to script user-defined functions if there are no functions defined by users in the database:

5- Based on the selections you have made in the previous page, you will get one or more pages to select the objects to script. If you have chosen to script only tables, you will see a page to select the tables to script:

6- In the output option, you will get the options for the script mode. You can the destinations of type File, Clipboard, or Query Window. I suggest to script the definition of your objects to a Query Window, because it is more readable in SSMS:

7- Check objects listed in the Script Wizard Summary and click Finish. The Generate Script Progress is done, a new window containing the generated script in SSMS will open.You can look for the objects in here by pressing Crtl+F:

P.S. : I owe a special thanks to Hendra, a very good friend and colleague of mine. Thanks mate. 🙂