[OBIEE 11g] Importing Multi Dimensional Metadata

 

www.sssbi.com

OBIEE 11g Importing Multidimensional Metadata

Using the OBIEE Administration Tool, you can add multidimensional data sources to the Physical layer of the repository. Using multidimensional data sources enables the Oracle BI Server to connect and extract data from sources such as:

• Essbase
• Hyperion Financial Management
• Microsoft Analysis Server
• SAP/Business Warehouse (SAP/BW)

The differences between setting up multidimensional and relational data sources lie in the Physical layer. The business model setup is almost identical.

When importing multidimensional data sources, each cube is imported as a single, physical cube in the Physical layer. Oracle BI server imports each cube's metrics, hierarchies, and dimensions. After importing, ensure that each column has the correct aggregation rule and the default member type ALL is imported for each hierarchy. As a best practice, remove hierarchies and columns from the Physical layer that are not used in the business model. This removes unnecessary objects and may improve performance.

In the example below, we are creating a business model with an Essbase data source.

Below are the steps to create a multidimensional business model:

• Set up an Essbase data source

• Import metadata

• Verify the import

• Choose options to control the model

• View members and update member counts

• Create the business model

• Create Presentation layer

• Verify the results

Set up an Essbase data source

Oracle BI Server uses the Essbase client libraries to connect to Essbase data sources. For this example, I will assume the client libraries are already installed. If they are not, you must install the Essbase client libraries on each computer where you are running the Administration tool.

 

Import Metadata

As a best practice use the File Import option within the Administration tool.

 

When you import Essbase data sources, the cube's metadata is mapped to the Physical layer, so it supports the logical model. Metadata that applies to all members of a dimension, such as aliases, are modeled as dimension properties by default. Level based properties are mapped as separate cube columns in the dimension. Column types such as Ancestor Reference, Member Key, Leaf, etc. are used by the system internally and should not be changed.

As a best practice rename your connection to a meaningful name. In this case use, "Essbase", to match identify it as an Essbase connection.

Choose options to control the model

There are different options in the Physical layer that let you control how you want to model the metadata. You must choose the option that best suits your business needs.

By default the Essbase metadata is modeled as dimensional properties. This multidimensional structure works best for the new hierarchal reporting style in the current OBIEE release. You can also choose to flatten your data in the Physical layer with the attribute style reporting that was supported in prior OBIEE releases.

By default, measures are imported as measure hierarchies. Meaning, each cube contains a single measure column that represents all the measures. As another option, you can flatten the measure hierarchy to view each measure as a separate column.

To do this, right click on the cube object and select, "Convert measure dimension to flat measures".

 

View members and update member counts

You can view members and update member counts to verify connectivity. You must open the repository in online mode to update the member count. You can view members in offline or online mode. Place your cursor over the hierarchy or level name to determine if the counts need to be updated. If they do need to be updated, a message will inform you. When you update member counts, the current member(s) is returned from the selected hierarchy. After updating, a message will appear when you place the cursor over the hierarchy or level name.

Create the business model

To create the business model, drag the physical cube to the logical layer. All logical tables, dimensions and relationships are created automatically. You can modify the Business Model and Mapping layer if needed.

As a best practice prefix your fact tables with "fact-", and your dimension tables with "dim-". Also, remove any member key columns that are not needed in the Business layer. Many times, there are only used by the system in the Physical layer.

Create Presentation layer

To create the Presentation layer, drag the business model to the Presentation layer. You can modify the Presentation layer if needed. Make sure to check for consistency and mark available for queries before saving the repository.

Verify the results

To verify the results, log into Oracle BI Answers and build and execute a query. You can also verify the SQL in the query log.