| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Standard Edition One Tutorial Release 10g (10.1.3.2.1) E10312-01 |
|
Previous |
Next |
In this chapter, you build the Oracle BI metadata repository from the BISE1_TUTORIALWH data mart you just created.
Note that some of the later sections in this chapter have been flagged as "Advanced." You do not need to work through these topics during your initial pass through the tutorial, unless you wish to do so.
Prerequisites: If you have not completed the previous chapter, you must first populate the BISE1_TUTORIALWH schema before proceeding with this chapter. Section 2.2.2.3, "BISE1_TUTORIALWH Schema" explains how to do this.
This chapter contains the following topics:
Section 4.3, "Building the Business Model and Mapping Layer"
Section 4.7, "Using Initialization Blocks and Variables - Advanced"
Section 4.8, "Executing Direct Database Requests - Advanced"
The Oracle BI Administration Tool is used to maintain BI metadata repositories. It provides a graphical representation of the three layers of the repository: a) Physical Layer, b) Business Model and Mapping Layer, and c) Presentation Layer. Each of these layers is explained later. You use the Oracle BI Server Administration tool to:
Import metadata from database and other data sources to create the physical layer of the BI repository.
Simplify and reorganize the imported metadata into a business model.
Create the presentation layer from the business model, for presentation to users who request business intelligence information through Oracle BI Answers.
The Physical layer defines the data sources to which Oracle BI Server submits queries and the relationships between physical databases and other data sources that are used to process multiple data source queries. The recommended way to populate the Physical layer is by importing metadata from databases and other data sources. The data sources can be of the same or different varieties. You can import schemas or portions of schemas from existing data sources. Additionally, you can create objects in the Physical layer manually.
When you import metadata, many of the properties of the data sources are configured automatically based on the information gathered during the import process. After import, you can also define other attributes of the physical data sources, such as join relationships, that might not exist in the data source metadata. There can be one or more data sources in the Physical layer, including databases, spreadsheets, and XML documents. In this example, you import and configure tables from BISE1_TUTORIALWH schema, where you created your data mart using Warehouse Builder.
This section contains the following topics:
An ODBC data source is needed to import data source schema information into an Oracle BI Server repository. An ODBC data source has been pre-created during Oracle BI Standard Edition One installation.
Verify that the ODBC data source is configured correctly and can connect to the database, as follows:
Click Start > Programs > Administrative Tools > Data Sources (ODBC) to open the ODBC Data Source Administrator. Click the System DSN tab.
Select bise1db in the System Data Sources list, then click Configure.
Click Test Connection in the Oracle ODBC Driver Configuration dialog box. Change the User Name to BISE1_TUTORIALWH. Enter the password for the BISE1_TUTORIALWH database account. Click OK.
The BISE1_TUTORIALWH database account password was defined during installation.
You should see a Connection successful message. Click OK.
Click OK to exit the ODBC Data Source Administrator.
If you do not see bise1db in the System Data Sources list, then create it by following these steps:
Click Add in the ODBC Data Source Administrator dialog box.
In the Create New Data Source dialog box, select the Oracle in BISE1Home1_1 database driver. Click Finish to open the Oracle ODBC Driver Configuration dialog box.
In the Oracle ODBC Driver Configuration dialog box, enter BISE1DB for Data Source Name, select the appropriate TNS Service Name from the drop-down list (BISE1DB), and enter BISE1_TUTORIALWH as the User ID for the schema.
Click Test Connection to open the Oracle ODBC Driver Connect dialog box. Enter the password for the BISE1_TUTORIALWH database schema and click OK.
You should see a Connection successful message. Click OK.
Click OK in the Oracle ODBC Driver Configuration dialog box. Verify that the BISE1DB system data source is added in the ODBC Data Source Administrator, then click OK to close the ODBC Data Source Administrator.
In this step, you restart the Oracle BI Server service to load the bise1 repository into memory, as follows:
Select Start > Programs > Administrative Tools > Component Services. Double-click Services.
Restart the Oracle BI Server service. Verify that the Oracle BI Presentation Server and Oracle BI Java Host services are started. If they are not started, start them. The services can be started in any order. Close the Component Services window.
To import the dimensions and fact tables from the BISE1_TUTORIALWH schema into the repository, perform the following steps:
Click Start > Programs > Oracle Business Intelligence > Administration to open the Oracle BI Administration Tool. Click File > Open > Online.
In the Open Online AnalyticsWeb dialog box, type Administrator as userid and password. The password is case sensitive. Click Open.
The repository will open with BISE1_SALESWH in the Physical Layer and its corresponding GEC_DW Business Model and Presentation Layer in online mode. GEC_DW is a prebuilt subject area created from the prebuilt data mart, BISE1_SALESWH. Explore the repository. You will be creating a similar BI metadata repository based on the BISE1_TUTORIALWH data mart.
|
Note: In online mode, Oracle BI Server has opened the repository file and the operating system has put a write-lock on the file. In this mode, Oracle BI Server can act as an agent of the Administration Tool. When the Administration Tool tells it to, Oracle BI Server sends the Administration Tool a copy of its in-memory repository. Then it listens for messages from the Administration Tool about changes, makes those changes to its in-memory copy, and, when told by the Administration Tool, tells the operating system to save the changed file.When you start the Administration Tool in online mode, you pick an Oracle BI ODBC DSN that points to the repository you want to edit (in other words, the repository that is the default repository in the DSN). The Administration Tool then communicates changes to Oracle BI Server, and Oracle BI Server makes the corresponding changes to its in-memory copy. You can also work in offline mode. In offline mode, the relationship between the Administration Tool and the repository is like the relationship between any Windows application and a file. An application, the Administration Tool in this case, opens a file for editing, makes changes to its in-memory copy, and tells the operating system to save the changed file. Typically, you develop a repository in offline mode and use online mode for minor updates and changes. |
Import the tables from the BISE1_TUTORIALWH schema. Select File > Import > from Database, then select the BISE1DB ODBC connection. Enter BISE1_TUTORIALWH as the User Name. Enter the Password for the BISE1_TUTORIALWH database account. Click OK.
In the Import dialog box, expand the BISE1_TUTORIALWH schema folder and use Ctrl + Click to select the following tables: CHANNELS, GEOGRAPHY, PRODUCTS, PROMOTIONS, SALES, and TIMES.
|
Note: Do not select the WB_RT_VERSION_FLAG table for import. That is an internal table used by Oracle Warehouse Builder, and is not needed for the BI metadata repository. |
Tables and Keys are the default options selected. Because you are familiar with the underlying schema structure and know that there are foreign keys already defined, go ahead and choose to import the foreign keys by selecting the Foreign Keys option. Click Yes when prompted about whether you wish to proceed with importing the foreign keys.
If you were not aware of the joins in the schema, or your queries need different joins, it is better to select only Tables and Keys, and create physical joins later.
Click Import. The Connection Pool dialog box opens.
In the Connection Pool dialog box, on the General tab, verify that the call interface is set correctly. Select Default (OCI 10g) from the drop-down list. Change the data source name to the appropriate tnsnames.ora entry (BISE1DB is used for the tutorial). Note that this is the TNS service name, not the ODBC DSN.
Leave the rest of the settings as they are and click OK to close the Connection Pool dialog box.
After the Import completes, click Close to exit the Import dialog box.
In the Physical layer of the repository, expand the bise1db > BISE1_TUTORIALWH schema folder, and verify that the correct tables are imported.
Right-click the BISE1_TUTORIALWH folder. Select Rename from the pop-up menu, and rename the folder to GEC_DW_TUTORIAL.
Check in the changes by selecting File > Check In Changes. To verify connectivity, select Tools > Update All Row Counts. When prompted about checking out the objects, click Yes.
When Update All Row Counts completes, verify that the row counts are displayed in the Physical layer of the Administration Tool. Select Tools > Options. Select Show row count in physical view, then click OK.
You should see the row counts for each of the tables, as shown in Figure 4-3.
In this section, you use the Oracle BI Administration Tool to build the Business Model and Mapping layer of a repository.
The Business Model and Mapping layer of the Administration Tool defines the business, or logical, model of the data and specifies the mappings between the business model and the Physical layer schemas. This is where the physical schemas are simplified to form the basis for the users' view of the data. The Business Model and Mapping layer of the Administration Tool can contain one or more business model objects. A business model object contains the business model definitions and the mappings from logical to physical tables for the business model.
The main purpose of the business model is to capture how users think about their business using their own vocabulary. The business model simplifies the physical schema and maps the users' business vocabulary to physical sources. Most of the vocabulary translates into logical columns in the business model. Collections of logical columns form logical tables. Each logical column (and hence each logical table) can have one or more physical objects as sources.
There are two main categories of logical tables: fact and dimension. Logical fact tables contain the measures by which an organization gauges its business operations and performance. Logical dimension tables contain the data used to qualify the facts.
This section contains the following topics:
To create a business model:
In the Business Model and Mapping layer, right-click the white space and select New Business Model.
In the Business Model dialog box, name the business model GEC_DW_TUTORIAL and leave the Available for queries box unchecked. The Description field is used to add a comment for yourself or another developer. Leave it empty.
Click OK to close the Business Model dialog. The new GEC_DW_TUTORIAL business model appears in the Business Model and Mapping layer. The red symbol on the business model indicates it is not yet enabled for querying. You enable the business model for querying later, after the Presentation layer is defined and the repository passes a global consistency check.
You can manually define the logical tables, joins, and columns in the business model. Or, you can allow the BI Administration Tool to automatically create them by simply dragging Physical layer objects over to the Business Model and Mapping layer. In this tutorial, you will use the automatic definition. For details on how to manually create logical tables, joins, and columns, consult the Oracle BI Administration Tool product documentation.
To populate the business model:
In the Physical layer, select all the tables in the GEC_DW_TUTORIAL folder, and drag them over to the GEC_DW_TUTORIAL business model folder.
This action automatically creates logical tables in the Business Model and Mapping layer. Notice that the SALES logical table has a yellow table icon. In the Business Model and Mapping layer, this indicates a fact table. Dimension tables are denoted with white table icons.
Expand the SALES logical table, then Sources to see the logical table source that was created automatically. Logical table sources define the mappings from a logical table to a physical table. A logical table's Sources folder contains the logical table sources. The logical table source name, SALES, is the same name as the physical table. However, it is possible to change names in the Business Model and Mapping layer without impacting the mapping.
In the Business Model and Mapping layer, double-click the AMOUNT logical column to open the Logical Column dialog box. Click the Aggregation tab. In the Default aggregation rule drop-down list, select SUM. Click OK.
Save the repository. Click OK to check in changes, but answer No for consistency checking. Leave the Administration Tool and the repository open for the next topic.
|
Note: Checking Global Consistency checks for errors in the entire repository. Some of the more common checks are done in the Business Model and Mapping layer and Presentation layer. Since these layers are not defined yet, bypass this check until the other layers in the repository are built. You learn more about consistency checking later in this tutorial. |
To rename business model objects:
Click Tools > Utilities. In the Utilities dialog box, click Rename Wizard and then click Execute.
In the Rename Wizard, scroll the middle pane. Click the Business Model and Mapping tab and select the GEC_DW_TUTORIAL business model, then click the Add Hierarchy button. Click Next.
Select Logical Table and Logical Column only. Click Next.
Select All text lowercase. Click Add.
Select First letter capital. Click Add.
Click Next and review changes. Click Next again.
Click Finish and verify that logical tables and logical columns in the Business Model and Mapping layer are changed as expected.
Rename the following columns in the Channels logical table:
Total_name to Channel_total
Class_name to Channel_class
|
Note: To rename an object, right-click it, then select Rename from the pop-up menu. You can also rename by selecting the column and gently clicking on the name to open an edit box. |
Rename the following columns in the Times logical table:
Calendar_year_name to Year
Calendar_month_description to Month
Calendar_quarter_name to Quarter
Rename the following columns in the Geography logical table:
City_name to City
Province_name to Province
Country_name to Country
Subregion_name to Subregion
Region_name to Region
Total_name to Geography_total
Rename the following columns in Products:
Category_name to Product_Category
Subcategory_name to Product_Subcategory
Rename the following columns in Promotion:
Subcategory_name to Promotion_Subcategory
Category_name to Promotion_Category
In Sales, rename Amount to Amount_Sold.
Save the repository. Click OK to check in changes, but answer No for consistency checking.
To delete unnecessary business model objects:
For the Channels logical table in the Business Model and Mapping layer, use Ctrl + Click to select the Channel_id, Channel_source_id, Class_id, Class_source_id, Total_id, and Total_source_id logical columns. Right-click either of the highlighted columns and select Delete to delete the columns. Alternatively, you can use the Delete key on your keyboard. Click Yes to confirm the delete.
Verify that the Channels logical table now has only four logical columns, as shown in Figure 4-12.
Repeat the steps to delete some of the logical columns in the Geography table. After you complete this step, you should only have the logical columns shown in Figure 4-13.
Repeat the steps to delete some of the logical columns in the Products table. After you complete this step, you should only have the logical columns shown in Figure 4-14.
Repeat the steps to delete some of the logical columns in the Promotion table. After you complete this step, you should only have the logical columns shown in Figure 4-15.
Repeat the steps to delete some of the logical columns in the Sales table. After you complete this step, you should only have the logical columns shown in Figure 4-16.
Do not delete any columns in the Times table.
Save the repository. Click OK to check in changes, but answer No for consistency checking.
Dimension hierarchies introduce formal hierarchies into a business model, allowing Oracle BI Server to calculate useful measures and allowing users to drill down to more detail. In a business model, a dimension hierarchy represents a hierarchical organization of logical columns belonging to a single logical dimension table. Common dimension hierarchies used in a business model are time periods, products, customers, suppliers, and so forth.
Dimension hierarchies are created in the Business Model and Mapping layer, and end users do not see them in end user tools such as Oracle BI Answers or Interactive Dashboards. In each dimension hierarchy, you organize dimension attributes into hierarchical levels. These levels represent the organizational rules and reporting needs required by your business. They provide the structure that Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data. Dimension hierarchy levels are used to perform aggregate navigation, configure level-based measure calculations, and determine what attributes appear when Oracle BI users drill down in their data requests.
To build dimension hierarchies:
Right-click the Channels logical table and select Create Dimension. Click Yes if prompted on whether you want to check out the objects. Right-click the ChannelsDim object, which was created by the action in the previous step, and select Expand All.
Verify that the ChannelsDim dimension hierarchy matches the one shown in Figure 4-17.
Right-click the Channels Detail level and select New Object > Parent Level. In the Logical Level dialog box, name the logical level Class and set the Number of elements at this level to 3. This number does not have to be exact. The ratio from one level to the next is more important than the absolute number. These numbers only affect which aggregate source is used (optimization, not correctness of queries). Click OK to close the Logical Level dialog box. The new Class level is added to the hierarchy.
Right-click the Class level and select Expand All. Drag the Channel_class column from the Channels Detail level to the Class level to associate the logical column with this level of the hierarchy.
Right-click Channel_class and select New Logical Level Key. In the Logical Level Key dialog box, verify that Channel_class and Use for drilldown are selected. The level key defines the unique elements in each logical level. Each level key can consist of one or more columns at this level.
Click OK to close the Logical Level Key dialog box. The Channel_class column now displays with a key icon.
Right-click the Class level and select New Object > Parent Level. In the Logical Level dialog box, name the logical level Channel Total Attribute and set the Number of elements at this level to 1. Close the dialog box.
Right-click the Channel Total Attribute level and select Expand All. Drag the Channel_total column from the Channels Detail level to the Channel Total Attribute level. Then right-click Channel_total and select New Logical Level Key.
In the Logical Level Key dialog box, verify that Channel_total and Use for drilldown are selected. Click OK to close the dialog box. The Channel_total column now displays with a key icon.
Right-click the Channel_name column and select New Logical Level Key. In the Logical Level Key dialog box, notice that Use for drilldown is selected. Click OK to close the Logical Level Key dialog. Both Channel_name and Dimension_key display with key icons.
Double-click the Channels Detail level to open the Logical Level dialog box. Click the Keys tab. Click Channels Detail_Key and then click Edit.
In the Logical Level Key dialog box, deselect Use for drilldown.
Click OK to close the Logical Level Key dialog box. Notice that the key icons are different. Channel_name is used for drill down, Channels Detail_Key is not. Later, when a user drills down in Answers or a dashboard, the default drill is to the level key that has Use for drilldown selected in the next lowest level. Based on this example, when a user drills down from the Channel Class column (the next highest level), the default is to drill down to the Channel_name column, not the Channels Detail_Key column.
Click OK to close the Logical Level dialog box. The finished ChannelsDim hierarchy should look like the one shown in Figure 4-29.
Save the repository. Click OK to check in changes, but answer No for consistency checking.
Next, you will build dimension hierarchies for the Products, Geography, Times, and Promotions logical tables. Use the preceding steps, the figures below, and the other characteristics below as a guide. If in doubt, you can check the dimensions in GEC_DW; the GEC_DW_TUTORIAL dimensions should be defined the same as the GEC_DW dimensions.
Build a dimension hierarchy for the Products logical table. Use the preceding steps, Figure 4-30, and the characteristics below as a guide.
The ProductsDim dimension hierarchy should have the following characteristics:
For the Subcategory level, set Number of elements at this level to 21.
For the Category level, set Number of elements at this level to 5.
For the Product Total Attribute level, set Number of elements at this level to 1.
For the Products Detail level, deselect Use for drill down for Products Detail_key.
Select Use for drill down for all other keys in the ProductsDim hierarchy.
If you complete this step, save the repository. Do not check global consistency.
Build a dimension hierarchy for the Geography logical table. Use the preceding steps and Figure 4-31 as a guide. You can accept the default number of elements at each level.
Build a dimension hierarchy for the Times logical table. Use the preceding steps and Figure 4-32 as a guide. You can accept the default number of elements at each level.
Build a dimension hierarchy for the Promotions logical table. Use the preceding steps and Figure 4-33 as a guide. You can accept the default number of elements at each level.
Save the repository. Click OK to check in changes, but answer No for consistency checking.
In this section, you use the Oracle BI Administration Tool to build the Presentation layer of a repository.
The Presentation layer is built after the Physical layer and Business Model and Mapping layer, and adds a level of abstraction over the Business Model and Mapping layer. It is the view of the data seen by end users in client tools and applications, such as Oracle BI Answers. The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users. For example, you can organize columns into catalogs and folders.
Simplifying the view of the data for users makes it easier to craft queries based on users' business needs, because you can expose only the data that is meaningful to the users, organize the data in a way that aligns with the manner in which users think about the data, and rename data as necessary for the set of users.
You typically create Presentation layer objects by dragging objects from the Business Model and Mapping layer. Corresponding objects are automatically created in the Presentation layer. Presentation layer objects can then be renamed and reorganized.
To build the Presentation layer:
Drag the GEC_DW_TUTORIAL business model from the Business Model and Mapping layer to the Presentation layer to create the GEC_DW_TUTORIAL catalog in the Presentation layer. Expand the GEC_DW_TUTORIAL catalog in the Presentation layer. Notice that the tables and columns in the Presentation layer exactly match the tables and columns in the Business Model and Mapping layer. Notice also that dimension hierarchies are not displayed.
Save the repository. Do not check global consistency.
You have finished building the initial business model and now need to test the repository before continuing your development. You begin by checking the repository for errors using the check consistency option. You then test the repository by running queries using Oracle BI Answers. Finally, you examine the query log file to verify the SQL generated by Oracle BI Server.
This section contains the following topics:
Consistency check is a utility in the Administration Tool that checks if a repository has met certain requirements. Repositories and the business models within them must pass the consistency check before you can make business models available for queries. When a repository or business model is inconsistent, a detailed message alerts you to the nature of the inconsistency.
The Consistency Check Manager displays three types of messages:
Error messages indicate errors that need to be fixed to make the repository consistent.
Warning messages indicate conditions that may or may not be errors, depending upon the intent of the Oracle BI Server administrator. For example, if the Administrator user has an empty password, this should be addressed, but is not a requirement for a consistent repository.
Best Practices messages provide information about conditions, but do not indicate an inconsistency. For example, if there are physical tables with no keys defined, a best practice message is displayed. Defining keys for physical tables is a best practice, but is not a requirement for a consistent repository.
For each message, the Consistency Check Manager identifies the message type, the object type, the object, and provides a detailed description of the message. There are options to display only selected message types, display results using qualified names, check all objects in the repository, and copy the results to another file.
To run a consistency check:
Select File > Check Global Consistency.
You should receive a message indicating that the repository is consistent and asking if you want to make it available for queries. Click Yes to make the GEC_DW_TUTORIAL business model available for queries. Click Yes if prompted to check out the objects. The Consistency Check Manager appears.
If the Consistency Check Manager displays any Error messages, edit the repository to correct the inconsistencies and run the consistency check again.
If you see only Warning and Best Practices messages, you can ignore the messages for now and click Close.
In the Business Model and Mapping layer, notice that the GEC_DW_TUTORIAL business model icon has changed to indicate the business model is now available for queries (the red circle with a line is gone).
Save the repository. Click No when asked to check global consistency (you just checked it).
Double-click the GEC_DW_TUTORIAL business model object to open the Business Model properties dialog box. Click Yes if prompted on whether you want to check out the objects. Notice that Available for queries is selected. Click OK to close the dialog box.
To enable query logging:
Select Manage > Security. In the Security Manager, select Users in the left pane. The Administrator user appears in the right pane.
In the right pane, double-click Administrator. Click Yes if prompted to check out the objects. The User dialog box opens. Verify that the User tab is selected. In the Logging level field, set the value to 2. Click OK.
To test the repository, you will need to generate some queries, retrieve the results, and examine the query log. You log query activity at the individual user level. Logging is intended for testing, debugging, and technical support. In production mode, logging is normally disabled because query logging can impact performance by producing very large log files.
Close the Security Manager window.
Check global consistency. You can ignore the warning messages. Click Close in the Consistency Check Manager dialog box.
Save the repository.
To use Oracle BI Answers to execute queries:
Select Start > Programs > Oracle Business Intelligence > Presentation Services. Log in to Oracle Business Intelligence as Administrator with password Administrator.
Click the Answers link, then click the GEC_DW_TUTORIAL subject area.
In the left pane, click the Geography folder to expand it. Notice that the folders and columns in Answers match the folders and column in the Presentation layer of the repository.
Click the Province column in the left pane to add it to the request criteria on the right. Click the Amount_Sold column from the Sales folder to add it to the request criteria.
Click the Results tab. By default, results are displayed in a compound layout, consisting of a title and a table view.
To use the query log to verify queries:
On the top right portion of the page, click Settings > Administration to open the Oracle BI Presentation Services Administration Window. Click the Manage Sessions link to open the Session Management Window.
In the Session Management Window, under Cursor Cache, click the View Log link for the last entry.
The log displays the last query executed by Administrator. The log file should look similar to Figure 4-42.
Locate the SQL Request section. This section contains the logical SQL issued from Answers.
Locate the General Query Info section, just below the SQL Request section. This section identifies the repository, subject area, and presentation catalog from which the query was run.
Locate the Sending query to database named BISE1_TUTORIALWH section, just below the General Query Info section. This section identifies the physical data source to which Oracle BI Server is connecting and the physical SQL that was generated.
The rest of the file contains information such as query status, number of rows returned, and so forth.
Close the query log. Close the Session Management window. Close the Oracle BI Presentation Services Administration window. Leave Answers open.
Often, a business wants to compare values of a measure and needs a calculation to express the comparison. Oracle BI Server has a calculation engine to perform a multitude of calculations. Calculation measures allow end users to ask business questions like "Show me the accounts receivable balance as of Q3" or "Show me the difference between units ordered and units shipped." An Expression Builder enables you to create expressions that are similar to expressions created with SQL. In the examples in this lesson, you use the Expression Builder to create calculation measures that appear as columns to users in Answers. Users can then easily build queries using familiar terminology.
There are different methods for creating calculation measures in the Administration Tool. You can use existing logical columns as objects in a formula, use physical columns as objects in a formula, or use the Calculation Wizard to automate the process. All three methods are covered in this tutorial. You use physical columns for calculations that require an aggregation rule to be applied after the calculation. You use logical columns for calculation formulas that require an aggregation rule that is applied before the calculation. You can also build calculation measures in Answers. The advantages to building calculation measures in the repository is that the measures are built once and can be made available to all users. The advantage of defining a logical column formula based on existing logical columns is that you only have to define it once. When you create formulas based on physical columns, you have to map for each physical source from which it could be derived.
This section contains the following topics:
Section 4.6.2, "Create a Calculation Measure Using Logical Columns"
Section 4.6.3, "Create a Calculation Measure Using Physical Columns"
Section 4.6.4, "Create a Calculation Measure Using the Calculation Wizard"
To create a new measure:
Return to the Oracle BI Administration Tool. In the Business Model and Mapping Layer, navigate to the Cost logical column under the Sales logical column.
Set the aggregation rule for the Cost logical column to Sum. Double-click the column to open the Logical Column properties dialog box and click the Aggregation tab. If prompted to check out objects, click Yes. Click OK.
Click File > Check In Changes or click the Check In Changes icon on the toolbar to check in changes.
Click Yes when prompted to check global consistency. The Consistency Check Manager opens and displays Warnings and Best Practices messages. Review the messages.
Click Close to close the Consistency Check Manager. Save the repository.
Return to Answers to test the new column in Answers. If Answers is not open, select Start > Programs > Oracle Business Intelligence > Presentation Server, log in as Administrator with password Administrator, click the Answers link, and click the GEC_DW_TUTORIAL subject area.
If Answers was already open, and if the Geography.Province column is already selected (in the request created in the previous section), remove it by clicking the X under the column name.
Click Reload Server Metadata.
Build the following request: Geography.Region, Sales.Amount_Sold, Sales.Cost. Select these columns from the left navigation tree, and then click the Results tab to view the results.
In this topic, you define a new calculation measure named Gross Profit in the Sales logical table, using existing logical columns to define the calculation formula. This measure can be used by any requests in Answers. There is a similar calculated measure called Profit in GEC_DW, which is used in several requests on the Sales Analysis dashboard.
To create a calculation measure using logical columns:
In the Oracle BI Administrator Tool, right-click the Sales logical table and select New Object > Logical Column.
In the Logical Column dialog box, name the logical column Gross Profit and select Use existing logical columns as the source.
Click the ellipsis (...) to open the Expression builder. In the left pane, click Logical Tables. Select Sales in the middle pane, then double-click Amount_Sold in the right pane to add it to the formula.
Click the minus sign operator to add it to the formula. Double-click Cost in the right pane to add it to the formula.
Click OK to close the Expression Builder. Notice that the formula appears in the Logical Column dialog box.
Click OK to close the Logical Column dialog box. The Gross Profit logical column appears in the business model. Check in the changes.
Drag the Gross Profit logical column to the Sales table in the Presentation layer. Check in the changes. Save the repository and return to Answers.
In Answers, click Reload Server Metadata. Expand Sales and verify that the Gross Profit column is now visible in Answers. Create the following request:
Geography.Region, Sales.Amount_Sold, Sales.Cost, Sales.Gross Profit
Click Results. Verify that the results are the same as the results shown in Figure 4-50.
Click Settings > Administration > Manage Sessions > View Log to view the query log. Verify that Gross Profit item was used.
Note that the difference between AMOUNT and COST is being calculated in the outer query block (SAWITH0.c2 - SAWITH0.c1 as c4 in the example shown in Figure 4-51). Because you defined the Gross Profit calculation using logical columns, the columns are summed first and then the difference is calculated. You compare these results to the query results in the next practice.
Close the query log, Session Management, and BI Presentation Services Administration windows.
In this section, you define a new calculation measure named Gross Profit Physical in the Sales logical table, using physical columns to define the calculation formula. To do this:
Return to the Oracle BI Administration Tool. In the Business Model and Mapping Layer, right-click the Sales logical table and select New Object > Logical Column.
|
Note: Click Yes to check out objects whenever prompted to do so. |
In the Logical Column dialog box, name the logical column Gross Profit Physical.
Click the Aggregation tab. Set the default aggregation rule to Sum.
Click OK to close the Logical Column dialog box. Gross Profit Physical is added to the business model.
Expand Sales > Sources and double-click the SALES logical table source. The Logical Table Source dialog box opens. Click the Column Mapping tab.
Select the Show unmapped columns option.
Click the ellipsis (...) for the Gross Profit Physical logical column.
In the Expression Builder, select Physical Tables > SALES > AMOUNT, then click Insert to add the column to the formula. Click the minus sign operator to add it to the formula.
Select Physical Tables > SALES >COST, then click Insert to add the column to the formula.
Click OK to close the Expression Builder. Notice that the expression is added in the Logical Table Source dialog box.
Close the Logical Table Source dialog box. The icon for Gross Profit Physical changes to indicate an aggregation rule is applied.
Drag Gross Profit Physical to Sales in the Presentation layer. Check in changes and save the repository. Return to Answers. Click Reload Server Metadata. Expand Sales and verify that the Gross Profit Physical column is now visible in Answers.
Create the following request:
Geography.Region, Sales.Amount_Sold, Sales.Cost, Sales.Gross Profit Physical
Click Results. Verify that the results are the same for the Gross Profit Physical (built using physical columns) as they were for the Gross Profit column (built using logical columns).
The calculation formula for the logical columns looks like this: sum(Amount_Sold) - sum(Cost), whereas the calculation formula for the physical columns looks like this: sum(AMOUNT_SOLD -COST). Because of arithmetic laws, you know that you can sum ColumnA and sum ColumnB and then take the differences of those sums, and have exactly the same results if you calculate the difference first (the value in ColumnA - the value in ColumnB for each row) and then sum the difference. So in this example, the results are the same for the logical column and the physical column calculations.
Click Settings > Administration > Manage Sessions > View Log to view the query log. Your results should look similar to Figure 4-58.
Note that the difference between Amount_Sold and Cost is calculated first and then summed: sum(T9278.AMOUNT- T9278.COST) in the example shown in Figure 4-58.
Close all the windows.
To create a calculation measure using the wizard:
Return to the Oracle BI Administration Tool. In the Business Model and Mapping Layer, right-click the Sales > Amount_Sold logical column and select Duplicate. A new column named Amount_Sold#1 is added to the business model. Rename Amount_Sold#1 to Category Sales.
Double-click Category Sales to open the Logical Column dialog box. Click the Levels tab and select Category as the logical level for ProductsDim. Click OK.
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. Close the dialog box.
Right-click Amount_Sold and select Calculation Wizard. The Calculation Wizard opens. Click Next. Select Category Sales as the column to compare with Amount_Sold. Click Next.
Deselect Change and Percent Change, but select Percent. Change Calculation Name to Share of Category Sales. Click Next, then click Next again if asked whether you want to check the objects out. Click Finish.
Share of Category Sales is added to the business model. Drag Category Sales and Share of Category Sales to Sales in the Presentation layer. Check in the changes. Save the repository.
In Oracle BI Answers, click Reload Server Metadata. Expand Sales and verify that Category Sales and Share of Category Sales are now visible in Answers.
You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. A variable has a single value at any point in time. Variables can be used instead of literals or constants in the Expression Builder in the Administration Tool. Oracle BI Server substitutes the value of a variable for the variable itself in the metadata.
You use the Variable Manager to define variables and initialization blocks. There are two classes of variables, repository variables and session variables:
A repository variable has a single value at any point in time. There are two types of repository variables: static and dynamic. Static repository variables have values that are constant and do not change while Oracle BI Server is running. Dynamic repository variables have values that are refreshed by data returned from queries in initialization blocks. Repository variables are represented by a question mark icon in the Variable Manager.
Session variables are created and assigned a value when each user logs on. There are two types of session variables: system and nonsystem. System variables have reserved names and are used for specific purposes by Oracle BI Server, such as authenticating users. Non-system variables are application-specific variables created by an Administrator. System and nonsystem variables are represented by a question mark icon in the Variable Manager.
Initialization blocks are used to initialize dynamic repository variables, system session variables, and nonsystem session variables.
This section contains the following topics:
Section 4.7.1, "Explore an Initialization Block for Session Variables"
Section 4.7.2, "Test the Initialization Block and Session Variables"
Session variables are like dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled.
When a user begins a session, Oracle BI Server creates new instances of session variables and initializes them. Unlike a repository variable, there are as many instances of a session variable as there are active sessions on Oracle BI Server. Each instance of a session variable could be initialized to a different value.
A session is an instance of a user running the client application. The session starts when the application is started and ends when the application is exited.
To explore an initialization block for session variables:
In the Oracle BI Administration Tool, click Manage > Variables to open the Variable Manager. Click Session > Initialization Blocks.
A session initialization block named setUser has already been created. Double-click setUser to open the Session Variable Initialization Block dialog box.
Click Edit Data Source to open the Session Variable Initialization Block Data Source dialog box. Click Browse, then select bise1db > Connection Pool in the Select Connection Pool dialog box.
Click Select and notice that the connection pool is visible in the Connection Pool field in the Session Variable Initialization Block Data Source dialog box.
In the Default Initialization String field, notice the initialization string. You may need to scroll to the right to see the entire string:
select ':USER', case when upper(':USER') = 'KURT' then 'Germany' when
upper(':USER') = 'KEIKO' then 'Japan' when upper(':USER')= 'CHARLES' then
'United Kingdom' when upper(':USER') = 'KAREN' then 'United States of America'
end, 'CountryManagers', 2 from Dual
Click OK to close the Session Variable Initialization Block Data Source dialog box. Notice that the initialization string and connection pool are visible in the Session Variable Initialization Block dialog box. Click Edit Data Target to open the Session Variable Initialization Block Variable Target dialog box. Notice that the four variables have been created: USER, UserCountry, GROUP, and LOGLEVEL.
Notice the order of the variables. The order is important. The order of the variables must match the order of the values for the variables in the initialization string in the initialization block.
Click OK to close the Session Variable Initialization Block Variable Target dialog box. The variables are displayed in the Variable Target section of the Session Variable Initialization Block dialog box. Close all the dialog windows.
Check in the changes and save the repository.
To test the initialization block and session variables:
Click Manage > Security to open the Security Manager. Click Groups in the left pane. In the right pane, double-click the group CountryManagers.
Click Permissions to open the User/Group Permissions dialog box. Click the Filters tab. Click the Add button.
Expand GEC_DW_TUTORIAL and click the Geography presentation table.
Click Select to add Geography to the Name field in the User/Group Permissions dialog box.
Click the ellipsis (...) on the right to open the Expression Builder (you may need to scroll to see the button).
Select Logical Tables > Geography > Country and then click Insert to add Country to the formula. Click the equals (=) operator to add it to the formula.
Select Session Variables > UserCountry and click Insert to add UserCountry to the formula as an argument in the VALUEOF() function. Compare with the expression in Figure 4-70.
Click OK to close the Expression Builder. The filter is added in the User/Group Permissions dialog box. Close the User/Group Permissions dialog box.
Close the Group dialog box. Close Security Manager. Check in the changes and save the repository.
Go to Oracle BI Answers. If you are already logged in as Administrator, log out, then log in using Keiko as the username, with no password.
Select the GEC_DW_TUTORIAL subject area. Create a new request with Country under Geography, and Amount_Sold under Sales.
Click the Results tab. Notice that the only country displayed is Japan.
Leave Answers open.
To create a dynamic repository variable:
In the Oracle BI Administration Tool, click Manage > Variables to open the Variable Manager. Click Repository > Initialization Block.
Right-click the white space and select New Initialization Block to open the Repository Variable Init Block dialog box. Name the initialization block getMaxSalesDate_01.
Click Edit Data Source to open the Repository Variable Init Block Data Source dialog box. Click Browse to open the Select Connection Pool dialog box.
Double-click the bise1db > Connection Pool object to add it to the Connection Pool field in the Repository Variable Init Block Data Source dialog box. In the Default Initialization String field, type the following SQL:
select dimension_key, calendar_year_name, calendar_month_description from times where dimension_key=(select max(times) from sales)
Click OK to close the Repository Variable Init Block Data Source dialog box. The connection pool and initialization string are added to the Repository Variable Init Block dialog box.
Click Edit Data Target to open the Repository Variable Init Block Variable Target dialog box.
Use the New button to create three variables: maxSalesDate_01, maxYear_01, and maxMonthDesc_01. The order is important. The order of the variables must match the column order in the initialization string.
Click OK to close the Repository Variable Init Block Variable Target dialog box. The variables appear in the Variable Target field in the Repository Variable Init Block dialog box.
Click Edit Data Source to open the Repository Variable Init Block Data Source dialog box. Click Test and verify that you get the results shown in Figure 4-74.
Close Results. Close the Repository Variable Init Block Data Source dialog box. The getMaxSalesDate_01 initialization block is displayed in the Variable Manager.
Select Repository > Variables > Dynamic to see the variables displayed in the Variable Manager. Close Variable Manager. Check in changes and save the repository.
In Oracle BI Answers, use the GEC_DW_TUTORIAL subject area to build a request with the following:
Times. Year, Sales.Amount_Sold
Click the Add Filter button for the Year column. In the Create/Edit Filter dialog box, click Add > Variable > Repository.
In the Server Variable field, type maxYear_01. Then, close the Create/Edit Filter dialog box and verify your results. Your result should look similar to Figure 4-76.
Users with the appropriate permissions can create and issue a database request directly to a physical back-end database. The results of the request can be displayed and manipulated within Oracle BI Answers, and subsequently incorporated into Oracle BI Interactive Dashboards and Oracle BI Delivers.
The following privilege settings in Oracle BI Presentation Services Administration control whether you can create and issue physical requests:
Edit Direct Database Requests: If this privilege is set for you, you can create direct database requests. By default, this privilege is set only for users defined as Presentation Server Administrators.
Execute Direct Database Requests: If this privilege is set for you, you can issue physical requests. By default, this privilege is not enabled for anyone. A Presentation Server Administrator can change it.
To execute direct database requests:
If you are not already logged in as the Administrator account, log out, then log back in to Oracle BI Presentation Services as Administrator.
In Oracle BI Answers, click Settings > Administration. In the Oracle BI Presentation Service Administration screen, click Manage Privileges to open the Privilege Administration screen.
In the Privilege Administration screen, scroll down to Answers. Verify that the Edit Direct Database Requests privilege is granted to the Presentation Server Administrators group. By default, the Administrator user is a member of this group. If this privilege is not granted to the Presentation Server Administrators group, then do so.
In Oracle BI Answers, click the Create Direct Request link. In the Connection Pool field, type the connection pool name for the GEC_DW_TUTORIAL data source enclosed in double quotes ("bise1db"."Connection Pool" in this example). In the SQL Statement field, type SELECT * FROM channels.
Click Validate SQL and Retrieve Columns to display the columns from the Channels table.
Click Results to see the contents of the Channels table.
Click Criteria, to return to the direct database request specification page. Remain here for the next section.
Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems. This eliminates the need for run-time calculations and delivers faster results to users. The calculations are done ahead of time and the results are stored in the tables. Aggregate tables should have many fewer rows than the non-aggregate tables, and therefore, processing should be quicker.
The aggregate navigation capability of Oracle BI Server allows queries to use the information stored in aggregate tables automatically, without query authors or tools having to specify aggregate tables in the queries. Oracle BI Server allows users to concentrate on asking the right business questions, because the server decides which tables provide the fastest answers. For Oracle BI Server to have enough information to navigate to aggregate tables, you need to configure certain metadata in the repository. You can also use the Aggregate Persistence Wizard utility in the Oracle BI Administration tool to create the aggregate tables.
This section contains the following topics:
Section 4.9.1, "Use a Direct Database Request to Create Aggregate Tables"
Section 4.9.3, "Create Physical Joins to the Aggregate Tables"
Section 4.9.5, "Test the Aggregation Using Oracle BI Answers"
To use a direct database request to create aggregate tables:
Using Windows Explorer, navigate to the directory where you installed Oracle BI Standard Edition One (the default installation directory is c:\oracle\bise1). Go to the tutorial\bi_ad directory, and open the aggregate table SQL script file: agg_table_sql.txt.
In Oracle BI Answers direct database request Criteria page, verify that the Connection Pool field is still set to the connection pool name for the BISE1_TUTORIALWH data source enclosed in double quotes ("bise1db"."Connection Pool" in this example).
Copy the first CREATE TABLE SQL for the first table, AGG_DAY_SALES_F, from the agg_table_sql.txt file and paste it into the SQL Statement field.
Click Results. You should see a No Results message.
Click the Criteria tab. Remove the CREATE TABLE SQL from the SQL Statement field and type SELECT COUNT(*) FROM AGG_ DAY_SALES_F in the SQL Statement field.
Click Validate SQL and Retrieve Columns. Click Results. You should see COUNT(*) = 229.
Click the Criteria tab. Remove the SELECT COUNT(*) SQL statement from the SQL Statement field. Copy the second Create Table SQL from the agg_table_sql.txt file, for the second table AGG_PRODUCTS_CATEGORY_D, and paste it into the SQL Statement field.
Click Results. You should see a No Results message.
Click the Criteria tab. Type the following SQL in the SQL Statement field: SELECT COUNT(*) FROM AGG_PRODUCTS_CATEGORY_D.
Click Results. You should see COUNT(*) = 6.
|
Note: You may see a "Downloads" window when you click the Results tab. You can ignore the window. |
In the Oracle BI Administration Tool, you need to import the two new aggregate tables from BISE1_TUTORIALWH schema. To do this:
Select File > Import > from Database, then select the BISE1DB ODBC connection. Enter the password for the BISE1_TUTORIALWH database account. Click OK.
|
Note: If the repository is not already opened, you can open it by selecting File > Open > Online. |
In the Import dialog box, expand the BISE1_TUTORIALWH schema folder and use Ctrl + Click to select the following tables:
AGG_DAY_SALES_F
AGG_PRODUCTS_CATEGORY_D
Note that Tables and Keys are the default options selected.
Click Import. The Connection Pool dialog box opens. Click Yes if prompted to check out objects.
After the Import completes, click Close to exit the Import dialog box.
In the Physical layer, drag the two imported tables from the BISE1_TUTORIALWH folder into the GEC_DW_TUTORIAL folder.
Delete the BISE1_TUTORIALWH folder from the physical layer by right-clicking the BISE1_TUTORIALWH folder and selecting Delete.
Expand the GEC_DW_TUTORIAL folder in the Physical layer and verify that the aggregate tables are added. Check in changes.
Select the two aggregate tables in the Physical layer, then right-click and select Update Row Count to verify connectivity. You should see 229 rows for AGG_DAY_SALES_F, and 6 rows for AGG_PRODUCTS_CATEGORY_D.
Check in the changes and save the repository.
To create physical joins to the aggregate tables:
In the Physical layer, use Ctrl+Click to select the two new aggregate tables and the Times table. Click the Physical Diagram icon on the toolbar.
In the Physical Diagram, use the New Foreign Key button to create the following joins. Click Yes when prompted to create matching table keys.
AGG_PRODUCTS_CATEGORY_D. CATEGORY_ID = AGG_ DAY_SALES_F. CATEGORY_ID TIMES.DIMESION_KEY = AGG_SALES_F.TIMES
|
Note: The default join columns selected may not be correct. Ensure that the correct columns are selected for the joins. |
If there is an error message, check the Physical column data type and make sure they are the same. You may have to manually change the datatype in the Physical Column dialog box. If a message says that the keys have to be created, click Yes.
Check in the changes and save the repository.
To map logical columns to aggregate columns:
Map existing logical columns to new sources by dragging columns from the Physical layer to corresponding columns in the Business Model and Mapping layer, as described in Table 4-1.
In the Business Model and Mapping layer, expand Sales > Sources and verify that a new logical table source, AGG_DAY_SALES_F, is created.
Double-click the AGG_ DAY_SALES_F logical table source to open the Logical Table Source dialog box, click the Column Mapping tab, and verify the mapping.
Click the Content tab. Use the drop-down menu in the Logical Level field to set the following logical levels:
ProductsDim = Category
TimesDim = Times Detail
You are setting aggregation content for the fact table to the corresponding levels in the dimension hierarchies. In a subsequent step, you set similar levels for the dimension table aggregate sources. Later, when a user queries against a particular level, Oracle BI Server will access the aggregate tables instead of the detail tables.
For example, after setting aggregation content, if a user queries for Amount_Sold by Product Category, the server will access the AGG_DAY_SALES_F aggregate fact table and the corresponding aggregate dimension table, AGG_PRODUCTS_CATEGORY_D.
If a user queries for a level lower than the levels specified here, for example, Product Subcategory or Product Name, then the server will access the detail tables. If a user queries for a higher level, for example, Total_Name, the aggregate tables will be used as well, because whenever a query is run against a logical level or above, the aggregate tables are used.
Click OK to close the Logical Table Source dialog box.
Expand Products > Sources and verify that a new logical table source, AGG_PRODUCTS_CATEGORY_D, is created.
Double-click the AGG_PRODUCTS_CATEGORY_D logical table source, click the Column Mapping tab, and verify the mappings.
Click the Content tab. Use the drop-down menu in the Logical Level field to set the following logical level to specify what level of detail is stored in the aggregate table:
ProductsDim = Category
Click OK to close the Logical Table Source dialog box. Check in changes. Check consistency. Fix any errors before proceeding.
Save the repository. Note that you did not need to make any changes to the Presentation layer. You made changes in the business model that impact how the queries are processed and which sources are accessed. Based on how you specified the aggregation content, Oracle BI Server automatically uses the new sources when appropriate.
To test the aggregation:
In Oracle BI Answers, click Reload Server Metadata.
Click the Answers link. Select the GEC_DW_TUTORIAL subject area.
Create the following query:
Products.Products_Category, Sales.Amount_Sold
Click Settings > Administration, then click Manage Sessions. Locate the last query run under Cursor Cache and click View Log.
Examine the log and verify that the aggregate tables AGG_CUSTOMER_STATE_D and AGG_DAY_SALES_F were accessed.
Close all windows.
The ability to compare business performance with previous time periods is fundamental to understanding a business. Time comparisons allow businesses to analyze data that spans multiple time periods, providing a context for the data. Yet, as Ralph Kimball states, SQL was not designed to make comparisons over time straightforward:
"The most difficult area of data warehousing is the translation of simple business analyses into SQL. SQL was not designed with business reports in mind. SQL was really an interim language designed to allow relational table semantics to be expressed in a convenient and accessible form, and to enable researchers and early developers to proceed with building the first relational systems in the mid-1970s. How else can you explain the fact that there is no direct way in SQL to compare this year to last year?"
- Ralph Kimball
The solution is to model time series data in the Oracle BI repository. This allows users to make one request for the desired result. Oracle BI Server runs multiple queries in parallel to get the results. The queries that run in the background to support the time measure are transparent to the user.
Oracle BI Server provides Ago and ToDate functions for time series comparisons. Both of these functions operate on measures. The Ago function calculates the aggregated value as of some time period shifted from the current time. For example, the Ago function can produce sales for every month of the current quarter, along with the corresponding quarter ago sales. The ToDate function is used to aggregate a measure attribute from the beginning of a specified time period to the currently displaying time. For example, the ToDate function can calculate Month to Date sales for a given year. You use the Expression Builder to apply the functions.
This section contains the following topics:
To identify a dimension as a time dimension:
In the Oracle BI Administration Tool, double-click the TimesDim dimension hierarchy in the Business Model and Mapping layer.
In the Dimension dialog box, select Time dimension. Click OK to close the Dimension dialog box.
Expand TimesDim to the Times Detail level, then double-click the Times Detail level to open the Logical Level dialog box. Click the Keys tab, then select the Chronological Key option for Times Detail_Key.
Click OK to close the Logical Level dialog box.
To create a Month Ago measure:
Right-click Sales and select New Object > Logical Column to open the Logical Column dialog box. Name the logical column Month Ago Dollars. Select Use existing logical columns as the source.
Click the ellipsis (...) to open the Expression Builder. Select Functions > Time Series Functions > Ago. Click Insert to add the Ago function to the Expression Builder.
Click the <<expr>> in the expression. Select Logical Tables > Sales and then double-click Amount_Sold to add it to the expression.
Click the <<level>> in the expression. Select Time Dimensions > TimesDim and then double-click Times Detail to add it to the expression.
Click <<integer>> in the expression and type 1. The Ago function calculates the Amount_Sold value one month prior to the current month.
Click OK to close the Expression Builder. The formula is displayed in the Logical Column dialog box.
Click OK to close the Logical Column dialog box.
To create a Change Month Ago measure, create another logical column called Change Month Ago Dollars with the expression shown in Figure 4-91. Be sure to select Use existing logical columns as the source.
To create a ToDate measure, create another logical column called Year To Date Dollars with the expression shown in Figure 4-92. Be sure to select Use existing logical columns as the source.
To test the time series measures:
Drag the three time series measures from the Business Model and Mapping layer to the Sales presentation table.
Check in the changes. Check for global consistency and save the repository.
In Oracle BI Answers, click Reload Server Metadata.
Create a new request using the GEC_DW_TUTORIALWH subject area.
Select Times.Year, Times.Month, and Times.Calendar_month_id.
Add a filter on Year=2006.
Sort in ascending order on Calendar_month_id. Then, hide the column. To do this, click Column Properties for Calendar_month_id, then, in the Column Format tab, select the Hide option.
Now add the following columns:
Sales.Amount_Sold
Sales.Month Ago Dollars
Sales.Change Month Ago Dollars
Your Answers result should look like the one shown in Figure 4-94.
In Criteria, delete Sales.Month Ago Dollars and Sales.Change Month Ago Dollars. Add Sales.Year To Date Dollars.
Your Answers result should look like the one shown in Figure 4-95.
Data is often partitioned into multiple physical sources for a single logical table in a business model. When a logical table source does not contain the entire set of data at a given level, you need to specify the portion of the set that it does contain. When individual sources at a given level contain information for a portion or fragment of the domain, Oracle BI Server needs to know the content of the sources in order to pick the appropriate source for the query. The goal is to provide seamless and efficient access from the users' perspective. When there are multiple sources, the metadata is built so that Oracle BI Server handles the navigation to the appropriate source. Oracle BI Server can seamlessly access and process data from multiple sources in an efficient manner to satisfy user requests.
In this example, sales quota numbers are stored in an Excel workbook. The workbook, Quota.xls, is stored on your computer. You incorporate the quota numbers into the business model and create business measures to report variance from quota and percent of quota.
This section contains the following topics:
To create an ODBC DSN:
Click Start > Programs > Administrative Tools > Data Sources (ODBC) to open the ODBC Data Source Administrator. Click the System DSN tab and click Add.
The Create New Data Source dialog box opens. In the Create New Data Source dialog box, select the Microsoft Excel Driver.
Click Finish to open the ODBC Microsoft Excel Setup dialog box. In the ODBC Microsoft Excel Setup dialog box, enter GEC_DW_TUTORIALQuota as the Data Source Name.
Click Select Workbook to open the Select Workbook dialog box and go to the location where you installed Oracle BI Standard Edition One. Then, go to the tutorial\bi_ad directory, select the Quota.xls file, and click OK.
The path to the workbook is displayed in the ODBC Microsoft Excel Setup dialog box. Click OK to close the ODBC Microsoft Excel Setup dialog box.
Verify that the Excel system data source is added in the ODBC Data Source Administrator, then click OK to close the ODBC Data Source Administrator.
To import the Excel data source:
In BI Administration Tool, select File > Import > from Database. In the Select Data Source dialog box, select the ODBC DSN you created in the previous steps. Leave User Name and Password blank.
Click OK. The Import dialog box opens. In the Import dialog box, select the GEC_DW_TUTORIALQuota object. Verify that only Tables and Keys are selected, and click Import.
|
Note: Each named range in the Excel worksheets are treated as a table. |
When the import completes, click Close to close the Import dialog box and verify that all four range tables have been imported into the Physical layer.
The Country table will not be used.
Check in changes and save the repository.
To create a physical key:
In the Physical layer, double-click the Quotas physical table.
Click the Keys tab, then click New.
Enter Quotas_Key as the Name of the key.
Select Prod Category, Year, and Calendar Quarter Desc. Click OK to close the Physical Key dialog box.
Click OK to close the Physical Table dialog box.
To create the physical joins:
Use Ctrl+Click to select table ProdCategory, XLDates, Quotas in the Physical layer. Right-click any one of the three tables and select Physical Diagram > Selected Object(s) Only to open the Physical Diagram.
In the Physical Diagram, click the New foreign key button to create the joins.
Click XLDates, then click and drag the join to Quotas. In the Expression field, enter the following expression, then click OK:
XLDates.CALENDAR_YEAR = Quotas."Year" AND XLDates.CALENDAR_QUARTER_DESC = Quotas."Calendar Quarter Desc"
When prompted for new key creation, click Yes.
Click ProdCategory, then click and drag the join to Quotas. In the Expression field, enter the following expression, then click OK:
ProdCategory.PROD_CATEGORY = Quotas."Prod Category"
When prompted for new key creation, click Yes.
The Physical Diagram should appear like the one shown in Figure 4-102.
Close the Physical Diagram.
Check in changes and save the repository.
To set the dimension key:
In the Business Model and Mapping layer, expand the TimesDim dimension.
Double-click the Quarter level.
In the Logical Level dialog box, click New.
Select Calendar_quarter_description. Click OK to close the Logical Level Key dialog box.
In the Logical Level dialog box, select the Chronological key option for Calendar_quarter_description.
Select Quarter and click Delete, and then click Yes to confirm the deletion.
Click OK to close the Logical Level dialog box.
To map the logical dimension columns:
Drag the physical column CALENDAR_YEAR from XLDates in the Physical layer to the logical column Times. Year in the Business Model and Mapping layer. Notice that a new logical table source, XLDates, is created automatically. Drag the remaining physical columns from XLDates in the Physical layer to the corresponding logical columns in the Times table in the Business Model and Mapping layer.
Double-click the XLDates logical table source in the Times > Sources folder to open the Logical Table Source dialog box, click the Column Mapping tab, and verify the mappings. Deselect Show unmapped columns.
Click OK to close the Logical Table Source dialog box.
Repeat the steps and map Products.Product_Category to ProdCategory.PROD_CATEGORY and verify the logical table source mapping.
To create the quota measures:
Drag the column Quotas.Quota from the Physical layer onto the Sales logical table. Notice that a new logical table source, Quotas, and a new logical column, Quota, are created.
Double-click Sales.Quota to open the Logical Table dialog box. Click the Aggregation tab and set the aggregation rule to Sum. Close the Logical Table dialog box.
The Quota logical column states quota in thousands, so rename Quota to Quota(000).
Create a measure for actual Quota based on Quota(000). To do this, right-click Sales and select New Object > Logical Column to open the Logical Column dialog box. Click the General tab and select Use existing logical columns as the source. Enter Quota in the Name field.
Click the ellipsis (...) to open the Expression Builder.
Specify the following formula:
1000*"GEC_DW_TUTORIALWH"."Sales"."Quota(000)"
Click OK to close the Expression Builder.
Click OK to close the Logical Column dialog box. The Quota column is added to the business model.
Create two more measures based on Amount_Sold and Quota. Right-click Sales.Amount_Sold and select Calculation Wizard. Click Next. Select the Quota column.
Click Next. Make sure Change is selected. In the Calculation Name field, name the calculation Variance from Quota.
Deselect Percent Change. Select Percent and make sure it is selected. In the Calculation Name field, leave the name as is: % of Quota.
Click Next. In the Finish window, verify the calculations that will be created by the Calculation Wizard.
Click Finish. The calculation measures are added to the business model.
Add the Quota (000), Quota, Variance from Quota, and % of Quota measures to the Sales folder in the Presentation Layer.
Check in the changes. Check global consistency. If you receive any Error messages, edit the repository to correct the errors before proceeding. Save the repository.
To test the quota measures:
Return to Oracle BI Answers. Log out and then log back in as Administrator with password Administrator. Click Reload Server Metadata.
Create the following request:
Times. Year, Sales.Amount_Sold, Sales.Quota, Sales.Variance from Quota, Sales.% of Quota
Because Quota is only for 2006, create a filter on the Year column as equal to 2006, as shown in Figure 4-113.
Click Results.
Click 2006 to drill down to the quarters. Your Answers result should look like the one shown in Figure 4-115.
Examine the query log and verify that Quotas and XLDates spreadsheets are accessed. Refer to Section 4.5.4, "Use the Query Log to Verify Queries" for detailed steps on viewing query logs.
