Introduction:
This documents describes step by step process to build a cube using Hyperion Integration Services. This can be used as a reference to build cubes from relational database or datawarehouse.
This document is intended for technical person who is familiar with Essbase and Integration services.
Hyperion Essbase
Hyperion Essbase is a three-tier application that complements Oracle Business Intelligence Suite Enterprise Edition.
Hyperion Essbase stores its data in special multidimensional databases that are optimized for fast access to aggregated data. Depending on the type of data being stored, Hyperion Essbase can create either “block storage” or “aggregate storage” databases, the former designed for smaller, denser data sets requiring read/write access and the latter designed for sparse, sales-analysis-style applications with lots of dimensions and read-only access. Data can be loaded into Hyperion Essbase databases by use of file imports or direct SQL extracts or through utilities that are provided alongside the Hyperion Essbase database server.
The most important single bit of information in a Hyperion Essbase database is the “outline,” which describes the dimensional and hierarchical structure of the database and details the dimensions, dimension members, hierarchies, measures, derived measures, and key performance indicators it contains.
You can either create a new database and its outline using the command line, or you can use tools such as Hyperion Essbase Administration Services and Hyperion Essbase Integration Services to create your database graphically and define its structure. Using these tools, you can either set up the database outline manually or derive this metadata from the data in a relational star schema.
Once your Hyperion Essbase database is defined and loaded, users of Oracle Business Intelligence Suite Enterprise Edition can import the metadata for their Hyperion Essbase database into their semantic model and report on it, along with their relational data, using Oracle Business Intelligence Answers, Oracle Business Intelligence Interactive Dashboards, and Oracle Business Intelligence Publisher. Oracle Business Intelligence Suite Enterprise Edition uses another Hyperion Essbase utility, Hyperion Essbase Provider Services, to provide native connectivity to Hyperion Essbase through the XML/A API and automatically converts the multidimensional data structures in your Hyperion Essbase database into the relational, dimensional data model that Oracle Business Intelligence Suite Enterprise Edition expects.
Hyperion Essbase Integration Services
With Hyperion Essbase Integration Services, you can quickly and easily load data into a Hyperion Essbase database and also create the database and derive the database outline at the same time, based on the hierarchies found in your relational source data. For more-complex data and scenarios, you can use a tool such as Oracle Data Integrator to transform your source data into a star schema, or you can manually load data into your Hyperion Essbase database dimensions and measures, using Hyperion Essbase Administration Services and direct SQL data requests.
Hyperion Essbase Integration Services has client and server elements and stores metadata about the relational data sources (referred to as the OLAP model) and the database outline you derive from it (known as the OLAP metaoutline) in a repository, which you can install into an Oracle Database instance. To connect to the Essbase Integration Services Console, provide the username and password for the Hyperion Essbase Integration Services repository and the username and password for a Hyperion Essbase server.
After you log in, the Essbase Integration Services Console presents you with a wizard that enables you to create new OLAP models and met outlines, open existing ones, or display ones you have recently edited or created.
As previously mentioned, creating a new Hyperion Essbase database by using Hyperion Essbase Integration Services involves two distinct phases:
• You create an OLAP model that defines the relational star schema used to define and then load your Hyperion Essbase database. You derive an OLAP metaoutline from your OLAP model that defines the dimensional structure (the “database outline”) of your Hyperion Essbase database.
To start creating your OLAP model, click the OLAP model icon and the Open button. You will then be presented with a dialog box in which you can select the data source that connects to your source data. Using the Data Source drop-down list, select the data source that points to the SH sample schema (if you haven’t already created this, go back and create it now) and enter the username and password to connect to it.
Note that if this is the first time you have accessed the SH schema, you may need to unlock the account or change the password; to do this, log in to your Oracle Database instance as a user with the DBA role and issue the following commands to unlock the account and set the password to “password”:
Alter user SH account unlock;
Alter user SH identified by password;
Once you have successfully connected, the Essbase Integration Services Console will appear, ready for you to define your OLAP model. If you have not already done so, you can click Intelligent Help on the application toolbar to display contextual guidance on preparing your data.
Creating your OLAP model involves three steps:
• Define your OLAP model fact table, which contains the columns that will go on to provide the data for your Hyperion Essbase database measures and define the dimensions associated with this fact table
• Define the aggregation hierarchies for your dimensions
• Finally, you edit the Account dimension to define which fact table columns are, in fact, measures (as opposed to foreign key columns)
Hyperion Essbase databases are “hypercubes,” which means that every measure in the database is dimensioned by all of the dimensions. A Hyperion Essbase database translates easily into a relational star schema, where a single fact table joins to one or more dimension tables and the OLAP model you are now creating is a representation of this star schema, together with the hierarchies in the star schema dimension tables.
The first step in defining the OLAP model is to pick the fact table, which you can do by either selecting Tools -> Create Fact Table from the application menu or locating the SH.SALES table in the list of database objects on the left-hand side of the Essbase Integration Services Console and dragging it onto the panel on the right-hand side. When selecting the fact table, select No when you are asked whether you would like to create a Time dimension (you will do this manually in a moment) and select Yes when you are asked whether you would like to create an Accounts dimension.
When you have selected your fact table and answered the above questions, the Essbase Integration Services Console should look like it does in the following screen shot:
The Accounts dimension is a type of dimension that details the measures within your database, and you will return to it later. For now, though, your next step is to define the dimensions in your OLAP model. To do this, either drag and drop the SH.CHANNELS, SH.CUSTOMERS, SH.TIMES, SH.PROMOTIONS, and SH.PRODUCTS tables onto the OLAP model diagram or have the Essbase Integration Services Console select these tables for you automatically (based on their foreign key joins to the fact table), by selecting Tools -> Create Dimension from the application menu.
If you choose the latter option and have the Essbase Integration Services Console add the dimension tables for you, you will be prompted after a few seconds to accept the autodetected dimensions, as shown in the screen shot below:
Click OK to accept these dimension tables, and review the updated OLAP model, which should show your dimension tables surrounding, and joined to, your fact table, as in the screen shot below:
Note that the CUSTOMERS table appears to have been added twice to the OLAP model; one of these tables is, in fact, the COUNTRIES table, which joins to the CUSTOMERS table and contains details on the countries and regions in which customers are located.
Now that you have added the dimension tables to your OLAP model, your next step is to define hierarchies for each of the dimensions. To do this, double-click the PRODUCTS dimension table, select the Hierarchies tab, and then click the Add button to start creating the product dimension hierarchy.
Using the list of columns on the left-hand side of the dialog box, select the following columns in the order specified:
• PROD_TOTAL
• PRODUCT_SUBCATEGORY_DESC
• PROD_NAME
The Hierarchy dialog box should then look like the following screen shot:
Creating your hierarchy in this way tells Hyperion Essbase Integration Services that in your OLAP model, the PROD_NAME column is aggregated into the PROD_CATEGORY_DESC column, which is, in turn, aggregated into the PROD_TOTAL column.
In our sample data, the PROD_NAME column contains some characters (double quotation marks) that are not considered valid Hyperion Essbase dimension member names. To fix this problem, select the PROD_NAME column and click the Transform button.
You now use the Transform function to replace any occurrences of double quotation marks in the product name with the value Inch. To do this, in the Transform dialog box, select the Replace tab and use the Search for and Replace All Occurrences fields in the dialog box to replace all occurrences of double quotation marks.
Click the Add to List button to add this step to the build instructions.
An alternative to using product names and customer names as your dimension member names is to use their IDs instead and place the names into dimension member aliases. Aliases do not have the same length and character restrictions as dimension member names and can be defined when you create the OLAP metaoutline. See the Hyperion Essbase documentation for more details.
Now create hierarchies for the TIMES, CHANNELS, PROMOTIONS, and CUSTOMER dimension tables, creating your hierarchies as follows:
Dimension Name Hierarchy
PRODUCTS PROD_NAME > PROD_CATEGORY_DESC > PROD_TOTAL
TIMES TIME_ID > CALENDAR_MONTH_NAME > CALENDAR_QUARTER_DESC > CALENDAR_YEAR
CHANNELS CHANNEL_DESC > CHANNEL_CLASS > CHANNEL_TOTAL
PROMOTIONS PROMO_NAME > PROMO_SUBCATEGORY > PROMO_TOTAL
CUSTOMERS CUST_ID > CUST_CITY > COUNTRY_NAME > COUNTRY_REGION > COUNTRY_TOTAL
Finally, double-click the TIMES dimension table and change the dimension type to Time, as shown in the screen shot below. Creating a Time dimension within your database gives you the ability to perform time series calculations, and although you will not use this feature in this example, it is good practice to define a Time dimension in case you will need these features in the future.
Use the File -> Verify option in the application menu to check the validity of your model, and if it is valid, select File -> Save and save your OLAP model.
Deriving the Metaoutline
Now that your OLAP model is complete, you can use it to derive the OLAP metaoutline.
To do this, close the OLAP model if it is still open and select File -> New from the Hyperion Essbase Integration Services application menu. You will then be presented, as before, with the Hyperion Essbase Integration Services welcome menu; this time click the OLAP metaoutline icon. From the drop-down list, choose the OLAP model you have just created, and click the Open button, as shown in the following screen shot:
When prompted, select the ODBC data source that connects to the SH schema, enter the connection details, and click OK to proceed.
When the Essbase Integration Services Console opens this time, it will be configured to enable you to create an OLAP metaoutline. If the Intelligent Help feature is not displayed, switch it on, using the application toolbar. The Essbase Integration Services Console should then look like the following screen shot:
When prompted, select the ODBC data source that connects to the SH schema, enter the connection details, and click OK to proceed.
When the Essbase Integration Services Console opens this time, it will be configured to enable you to create an OLAP metaoutline. If the Intelligent Help feature is not displayed, switch it on, using the application toolbar. The Essbase Integration Services Console should then look like the following screen shot:
You will now use the Intelligent Help feature to help you create the metaoutline.
Locate the Dimension and Members button in the Intelligent Help panel, and click it. In the list of Dimension and Members help steps that then appears, locate Create Standard Dimensions Automatically and click it. Then, when the contextual help text is displayed, locate the autodetect link and click it, which starts the process of automatically deriving metaoutline dimensions from your OLAP model.
The Essbase Integration Services Console then presents you with a list of all the hierarchies in your OLAP model. Click the Select All button, to select all of the hierarchies, and then the Create button, to create the metaoutline dimensions.
The new metaoutline, displayed in the bottom right-hand side of the Essbase Integration Services Console, should look like the following screen shot:
Now that you have derived the standard dimensions, your final step is to create the Accounts dimension in your metaoutline, which does not get created for you in the previous step.
To do this, drag and drop the Accounts dimension entry from the left-hand pane and drop it onto the metaoutline details, just below the last dimension in the list, and then drag and drop the AMOUNT_SOLD and QUANTITY_SOLD columns from the fact table onto this new dimension.
Use File -> Verify to check that the metaoutline is valid (you should receive the message “The metaoutline is valid for aggregate storage”) and then File -> Save to save the metaoutline to the Hyperion Essbase Integration Services repository.
Finally, you can set the storage mode for your Hyperion Essbase database. To do this, right-click the SH Metaoutline object on the right-hand panel of the Essbase Integration Services Console and select Properties. Using the Properties dialog box, select the Data Storage Model tab and select Block Storage as the storage type, as shown in the screen shot below.
Now that you have both an OLAP model and an OLAP metaoutline and have chosen your storage model, you can use them to define and then load your Hyperion Essbase database. To start this process, select Outline -> Member and Data Load from the application menu to bring up the “Member and Data Load” dialog box.
Hyperion Essbase Integration Services can create your Hyperion Essbase application and database for you if it is not already present: Type in Sales as the application name and Sales as the database name in the Essbase Name section of the dialog box. Next, in the Calc Scripts section, select Use Default Calc Script, which ensures that Hyperion Essbase Integration Services not only loads the detail-level data into your Hyperion Essbase database but also aggregates the data within it so that values are present at all levels of the cube.
Click Next and then Finish to start building the database immediately. Follow the progress of the database build in the “Member and Data Load” progress window, as shown in the following screen shot:
Check that no records were rejected during the load and that 57,906 dimension members are loaded, along with 916,782 fact table records. If you notice that records were rejected, it is probably because you didn’t use the Transform function in the Hierarchy editor to remove double quotation marks from the PROD_NAME column or because you selected different or additional table columns in your hierarchies, which can cause issues for unique member outlines, because column values are not unique across all tables. If problems occur, go back and fix them and run the “Member and Data Load” task again from the application menu.
When you have successfully built your Hyperion Essbase database, you can start Essbase Administration Services Console and take a look at the database that Hyperion Essbase Integration Services has just created for you.
To view the outline for your Hyperion Essbase database, locate the Sales database and double-click the Outline entry. You can now view the database outline as well as dimension members arranged into the hierarchies you defined by using Hyperion Essbase Integration Services.
You can also use the Essbase Administration Services Console to preview the data in your database. To do this, right-click your database and select Preview Data. Double-click the dimension headings to explore the data in your database.
If you find that some or all of the data in your database is missing, check that you did not run out of disk space when performing the calculation step in preparing your database—if Hyperion Essbase Integration Services runs out of disk space when performing the database calculation, it will stop at that point and leave some or all of the database uncalculated but otherwise not report an error. If this happens to you, free up the required space (the database, fully calculated, takes up around 1.8GB of disk space) or, alternatively, remove some of the rows from the SH.SALES table and reload, using a smaller data set.
Subscribe to:
Post Comments
(
Atom
)