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)
• 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.
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
• 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.
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.
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".
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.
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.
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.
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.
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.
No comments:
Post a Comment