Aggregate Persistance Wizard in OBIEE 11g

This exercise must be done on rpd’s which are deployed with the BI server is running

 Aggregate persistence is used to automate the creation and initial population of aggregate tables, persist them in backend datawarehouse schema and configures the rpd file to use them wherever its appropriate.  This is best way to deal with performance issue in datawarehouse tables for which intervention of ETL professionals is not needed.

Here are the steps to create an aggregate where a frequent query is fired for SALE_PRICE,MONTH and MODEL

Open rpd in online mode and and open the Utilities, choose “Aggregate Persistence”

Provide a name and location for the script file to be created

Select the metrics required to be persisted

Select the dimension level of the attributes that needs to be persisted. Check the use of “Surrogate Keys”

Select the Database, the schema and the connection pool and then specify the name of the aggregate table .

The created script now appears and simultaneously the script file is also created.

Once and check the script contents

Navigate to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin

Check that nqcmd.exe is present. This is a utility from OBIEE to execute scripts.

Cd to D:\OBIEE11G\Oracle_BI1\bifoundation\server\bin in a command window.

Issue the command nqcmd –d coreapplication_ON863956460 –u admin –p mypassword123 –s c:\agg_persistence.sql

Note that the entry after –d switch is the name of the OBIEE DSN. (to checn this Start>run>type odbcad32)

-u is the administrator username

-p is the administrator password

-s is script path

 Execute this command.

Once script executes, the command window displays the same as Processed. Note, any errors in this stage is because of 2 reasons :

·         BI Server is not running (opmnctl startall should be done) OR

·         The script is taken from an rpd that’s not deployed (take script from a rpd in online mode)

Once the script completes the changes done by it are visible.

·         New  tables are created in physical layer

·         Same are created in Database as well

·         New Logical table sources are created in BMM and mapped to respective columns

We can verify that column mapping to new LTS is done by opening the LTS’s created by the aggregate persistence wizard.

Notice that each of the objects created by aggregate persistence wizard in the rpd has a small “GEAR” symbol.

Calculation Wizard in OBIEE 11g

Create a Calculation Measure Using the Calculation Wizard

Create a Calculation Measure Using the Calculation Wizard

In this topic, you use the Calculation Wizard to define a new calculation measure named Share of Category in the Sales Facts logical table.

To create a calculation measure using the Calculation Wizard, perform the following steps:

1.	Return to the SH repository, which should still be open in online mode in the Administration Tool.
2.	Right-click the Amount Sold logical column and select Duplicate.
3.	A new column named Amount Sold#1 is added to the business model.
4.	Rename Amount Sold#1 to Category Sales.
5.	Double-click Category Sales to open the Logical Column dialog box.
6.	Click the Levels tab and select Category as the logical level for ProductsDim. Category Sales is now a level-based measure that will calculate total sales at the category level when used in a query. Level-based measures are useful for creating share measures. You use the Calculation Wizard to create a share measure in the steps that follow.
7.	Click OK to close the Logical Column dialog box.
8.	Right-click Amount Sold and select Calculation Wizard. The Calculation Wizard opens.
9.	Click Next.
10.	Check Category Sales as the column to compare with Amount Sold.
11.	Click Next.
12.	Uncheck Change and Percent Change and check Percent.
13.	Change Calculation Name to Share of Category.
14.	Click Next.
15.	Click Finish. Share of Category is added to the business model.
16.	Drag Category Sales and Share of Category to Sales Facts in the Presentation layer.
17.	Check in changes.
18.	Click Yes when prompted to check global consistency. If the repository is consistent (no Error messages), close the Consistency Check Manager. If there are Error messages, you must correct the errors before continuing.
19.	Save the repository.
20.	Return to Answers, which should still be open from the previous topic.
21.	Click Reload Server Metadata.
22.	Expand Sales Facts and verify that Category Sales and Share of Category are now visible in Answers.
23.	Place the cursor over the icon to display the screenshot and use it as a reference to create a query. Notice the sort settings for the Prod Category and Amount Sold columns.
24.	Expand the Times folder.
25.	Hold down the Ctrl key and click the Calendar Year column to open the Create/Edit Filter dialog box.
26.	Click the All Choices link.
27.	Click 2001 to add it to the Value field.
28.	Click OK to close the Create/Edit Filter dialog box. The filter is added to the request.
29.	Click Results. Only partial results are shown in the picture.
30.	Leave Answers open.

Look Up Tables in OBIEE 11g

Lookup table is a new feature in obiee 11g.
This is used mainly to:
• Resolve code columns and get description/name values from a lookup table.
• It can also be used for checking the currency conversion values from a daily updated source.


Lets try to see how this works.Assume that our customer table XW_CUSTOMER_D, has a column cust_state which is just the state code and not the state name:

We also have a table called STATE in the database which contains the state code and state names:

If we had a customer dimension and want to show the state name, instead of state code we need to create a lookup based on the STATE table.
Step-1
Import the STATE table into the physical layer of OBIEE and make an alias out of it.

Step-2
Define the Key column for the STATE table in the physical layer.

Step-3
Pull the lookup table to the BMM layer. You notice that it appears as a fact table with a # symbol in its icon(see arrow below). This is because the lookup table is not joined to any other table and OBIEE assumes it to be a fact table by default.


Step-4
Double click on Lkup_State_Name logical table in the BMM layer and check the “Lookup table” checkbox. In the Keys tab add a key which is based on the ID column.

Step-5
Define 2 new logical columns in the Dimn_Customers_D logical dimension table by right clicking on the lodical table name as follows :
These 2 columns are “Derived from existing columns using an expression”
1-Dense Looked Up State Name
Formula:
Lookup(DENSE "Sales"."Lkup_State_Name"."STATE_NAME",-> Lookup Value
"Sales"."Dimn_Customer_D"."Customer State"-> Lookup column
)

2-Sparse Looked Up State Name
Formula:
Lookup(SPARSE "Sales"."Lkup_State_Name"."STATE_NAME", -> Lookup Value
‘Lookup State Name not Found', ->String used when code not found(remove)
"Sales"."Dimn_Customer_D"."Customer State" -> Lookup column
)


We now have the 2 lookup column in place in the customer dimension as follows :

Step -6
Pull the new columns to the presentation layer into the customer dimension display folder and save your work.

---

Once done we create a report with the “Customer State” and “Sparse Looked up State Name”. The result will be as follows. Notice that code ‘UT1’ doesn’t have a corresponding lookup value in STATE lookup table and we see the string “Lookup State Name not Found”

This shows that “SPARSE” lookup causes an outer join between the customer and the lookup table.
The OBIEE server fires 2 SQL’s as follows and joins the results in the reporting layer.
SAWITH0 AS (select distinct T13265.CUST_STATE as c1
from
XW_CUSTOMER_D T13265 /* Dimn_CUSTOMER_D */ )
select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1
And
select T42371.STATE_NAME as c1,
T42371.ID as c2
from
STATE T42371 /* Lkup_State_Name */

Next we create a report with the “Dense Looked UP State Name” and we see the following result.

This shows that DENSE lookup executes an inner join between the customer and lookup table
We had a value ‘UT1’ which was not brought forward due to the inner join by the dense lookup, since that value was not present in STATE lookup table.
Moral :
Dense lookup = Inner join between Dimension and lookup table
Sparse Lookup=Outer join (with Nulls having a custom name) Dimension and lookup table

Creating an Implicit Fact Column in OBIEE

Setting or Creating an Implicit Fact Column in OBIEE

To set an implicit fact column follow the steps and screenshot

• Create a Dummy Fact column in a Physical Layer called Implicit Fact Column and map that column to any number of your choice

Creating Implicit Fact Column

• Join all Dimensions to this Fact column in BMM Layer
• In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
• In the general tab, find the Implicit Fact Column section.

Selecting Implicit Fact Column from Presentation Catalog

• Click on Set button, this will open the browse window
• Select a fact column from the fact tables available in the presentation catalog.
• Click OK.
• Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
• Click OK.
• Save your changes in the repository.

How-to: Mapviewer Integration with OBIEE 11g (11.1.1.6 and higher)

One of the biggest new features in BI EE 11g is the ability to create native mapviewer maps and then overlay reporting components/visualizations on top of maps. For example if you are doing a country based sales analysis, its easy to color code each country across the world right within the maps. It is also possible to overlay pie charts, bar charts in the maps thereby providing an extensive visual analysis capability. In 10g, one had to go through some complex java scripting to achieve BI EE and mapviewer integration. I have blogged about it here and here. 11g makes this entire integration a lot easier.

Lets try to understand the integration through a simple example. Shown below is a report of Sales performance of 3 US states across 4 years.

We would like to depict this using a map as shown below.

As you see we have 2 visualizations applied on the same map. The first one is a different color coding for each of the 3 states depending on the sales performance. The other is a pivot representation of yearly performance for each state, all within a single map.

To do this we start with enabling the Mapviewer integration by updating the instanceconfig.xml to include the following tags (in each presentation server)

<SpatialMaps>
<ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
<RemoteOracleMapViewerAbsoluteURL></RemoteOracleMapViewerAbsoluteURL> <LayerDataLayout>
<MaxRecords>600</MaxRecords>
</LayerDataLayout>
</SpatialMaps>

Then we need to restart the presentation service. This enables the connectivity between BI EE and Mapviewer. After enabling the integration, we then import the standard spatial MVDemo schema into the Oracle Database. This schema contains Layers, Themes and some Base maps for all the major US States, Cities & some inter-state highways. After importing and installing the MVDemo schema, we need to download the Mapbuilder application which will enable us to create a custom map. We will be using this map for rendering in BI EE. The Mapbuilder application can be downloaded from here.

After downloading the Mapbuilder app, we start the application and login to the MVDemo schema. By default this schema contains a lot of built-in themes, Base Maps and Tile Layers.

We start off with creating a simple Base Map called STATE_MAP which will be using 2 out of the box themes THEME_DEMO_STATES and THEME_DEMO_STATES_LINE

This step will enable us to render all the US States and its boundaries within a very simple map as shown below

Once the base map is created, lets create a new tile layer called STATE_TILE_MAP which will use this base map for rendering. We can define all the zoom levels, boundaries etc within the tile layer

Now we have a map that can show all the US states and its boundaries. We need to now expose this map to the mapviewer application (as an admin user through Mapviewer admin screen) by taking it online as shown below

Now the map is available for consumption through BI EE. Remember, the theme that we used above, THEME_DEMO_STATES has an identifier called STATE_ABBR which is basically the key that we need to pass from BI EE. Relationship between BI EE repository columns/subject areas and Mapviewer maps are done from the Manage Map Data screen in the presentation services admin console.

In this screen, we need to import the layers, maps and the images that we want to include as shown below

Edit the THEME_DEMO_STATES theme and map the State column from the repository. There need not be any relation between the column used in spatial and the column mapped in BI EE. All we need to ensure is the attributes should match. In my case, the State abbreviations come from the SH schema and the THEME_DEMO_STATES come from STATES table in the MVDemo schema.

Then navigate to the STATE_TILE_MAP in the background maps and add the THEME_DEMO_STATES again.

This establishes a relationship between the Subject Area and the Map thereby enabling us to add the mapview from answers. After doing this, lets create the report from the SH schema containing just the 3 states for 4 years.

Use the new Map View option to create the map.

This will render the map that we created in mapviewer. If you notice carefully, it will automatically apply some color coding to the 3 states that are part of the report.

The color coding is actually part of default visualization that Map Views apply on a map. Lets change this to something as shown below

Lets now create one more pie chart visualization (for obtaining the yearly breakup of sales in all the 3 states)

Lets use the Year as the column providing the necessary slices for the pie charts

This will give us the necessary visualization using Mapviewer.

Though there are a lot of steps involved, majority of the steps are for setting up the mapviewer. Once we have the maps set up the entire process of adding visualization to maps is quite straight forward.