Tutorial Decision Stream Cognos 5q7048

_ .log, where is the name of the build and is a three-digit number. For example, the log file for the build 2_Aggregation might be 2_Aggregation_001.log.

DecisionStream Tutorial

Builds

67

To access the log files: 1

From the Tools menu, choose Browse Log Files. The DecisionStream log folder opens.

2

To view a log file, double-click its icon.

§ §

If the ‘LOG’ extension has an association in Windows, the log file opens in the associated program. If the ‘.LOG’ extension has no association, the Windows Open With dialog box opens. In this dialog box: a.

Select a text editor, such as NOTEPAD.

b.

If you want LOG files to always open with your chosen text editor, then ensure the Always use this program to open this file check box remains selected.

c.

Click OK. The log file opens in your selected text editor.

Viewing the Delivered Data You can view the delivered data in SQLTerm or any application that can work with Microsoft Access databases. To view the data in SQLTerm: 1

Click Run SQLTerm tool on the toolbar. SQLTerm opens.

2

From the toolbar list, select Examples Datamart.

3

In the Database Objects pane, expand the Examples Datamart database and then control-drag the table ds_sales_conso to the SQL Query pane. SQLTerm constructs an SQL statement to return the entire table.

4

Click Execute SQL Query on the toolbar. SQLTerm retrieves the data and shows it in the lower pane.

68

Builds


Because this build performs aggregation, you can return summary data. For example, to return summary values for all products sold in the timezone ‘Eastern Standard Time’ during 1996, you would add the clause: WHERE period_no = '1996' AND state_cd = 'E' AND product_cd = 'ALL'


Builds

Anatomy of a Build In this section, you examine the various objects that make up the build. However, this is not a thorough examination and you should refer to the ’s Guide for full information on the components of a build.

The Build Tree In the Build window, fully expand the build 2_Aggregation by clicking the plus icon to the left of each node. The Build Tree should look like this:

Build ‘2_Aggregation’ fully expanded

In the Build Tree, you can examine the properties of most nodes by selecting the node and then performing one of these actions:

§

Click the Properties editor toolbar button.

69

70

Builds


§ §

Right-click the node and then choose Properties from the shortcut menu. Press F4.

The build consists of:

§ § § §

A DataStream A set of Build Elements A set of Procedures Delivery Modules, in three categories: −

Fact Delivery Modules

−

Dimension Delivery Modules

−

Metadata Delivery Modules.

DataStreams Each DataStream gathers a number of data sources to provide a consistent stream of data to the remainder of the build. You can add a data source to a DataStream by right-clicking the DataStream and then selecting Insert Data Source from the shortcut menu. Each data source can have an SQL statement that returns a number of data columns. The SQL statement can be any valid SELECT statement in the dialect of SQL of the DBMS. Therefore, the SQL statements can cope with source data that has a compound primary key. For example, the following is a valid statement in the dialect of Microsoft Access: SELECT country & state & city AS location, product, units_sold FROM sales WHERE product_type = k01;

This SQL statement returns three columns (location, product, and units_sold). The SQL statements of data sources can also contain s (if the DBMS s them). However, it is frequently more efficient to use many DecisionStream data sources than it is to specify s within SQL. With the exception of derivation elements, DecisionStream generates one DataStream item for each build element. It does not create DataStream items for derivation elements because DecisionStream does not acquire the data for these directly from the data sources. You can see how the data source


Builds

columns map to the DataStream items by selecting the DataStream node. The mappings appear in the Build Visualization pane.

Data Source to DataStream mapping

You can change these mappings in the Data Source Mapping properties dialog box.

Data Source Mapping properties dialog box

The right pane of this dialog box lists the DataStream items (Build Elements). You map a DataStream item to a data source column by dragging the item to the column.

Build Elements DecisionStream s four types of build element. These are:

§ § § §

Dimension elements – the key business components by which you want to analyze the data. Metric elements – the key measures of your business. Attribute elements – elements that are neither dimensions nor measures. For example, color. Derivation elements – elements that DecisionStream calculates from the source data.

71

72

Builds


Each element has a properties dialog box with which you can configure the element. Although you configure some elements in the examples that follow, you should refer to the ’s Guide for full information on element properties.

Procedures DecisionStream procedures are SQL statements that DecisionStream invokes during execution of the builds. The three types of procedures are:

§ § §

Pre – SQL statements that DecisionStream invokes at the start of build execution. Pre-Data – SQL statements that DecisionStream invokes after loading the reference data, but before it begins to acquire the fact data. Post – SQL statements that DecisionStream invokes after it has finished delivering the data.

The example builds do not make use of procedures. Therefore, you should refer to the ’s Guide for information about their use.

Delivery Modules As their name suggests, the delivery modules of a build deliver the fact data, dimension data, and metadata to the target data marts. There are three types of delivery module:

§ § §

Fact Delivery Modules – deliver fact data. Dimension Delivery Modules – deliver dimension data. Metadata Delivery Modules – deliver metadata specific to a particular OLAP tool.

By way of its delivery modules, a DecisionStream build can deliver data simultaneously to multiple databases and database management systems; it can simultaneously populate multiple OLAP targets; it can partition data both horizontally and vertically. You can disable individual delivery modules; you can disable all delivery modules of a particular type. Thus, DecisionStream delivery modules enable flexible, precise control over data delivery. Please refer to the ’s Guide for full information.


Builds

Multiple Data Sources In this build, you add a second data source to the build that you created previously. You will duplicate, and then modify, the previous build. While doing this, you will learn how to:

§ § § §

Duplicate an existing build Create new build elements Add a data source to an existing build Modify a dimension to accept source data at different levels of granularity.

Duplicating an Existing Build To duplicate an existing build: 1

Select the 2_Aggregation build and from the Build menu choose Duplicate. DecisionStream creates a duplicate, named ‘2_Aggregation:1’, of the build.

2

Rename the new build: a.

Select the new build and click Properties editor on the toolbar.

b.

In the Build Properties dialog box, enter 3_MultipleSource into both the Name and Caption boxes. Click OK.

c. 3

Rename the data source of the new build: a. Expand 3_MultipleSource and then its DataStream. b. c. d.

Select the data source 2_Aggregation and click Properties editor on the toolbar. Into the Name box of the Data Source Properties dialog box, enter SalesSource. Click OK.

73

74

Builds


Adding a Data Source The new data comes from the table ds_forecast in the Tutorial Sales database. You can inspect the structure of this table in SQLTerm. The following illustration shows this structure:

DecisionStream can accept source data at different levels of granularity. In this example, as suggested by the column quarter_no, the table gives forecast information by fiscal quarters. The table ds_sales gives information by fiscal periods. Therefore, to merge these data sources, DecisionStream must accept data at both the period and quarter levels of the Fiscal hierarchy. Two columns (units_forecast and revenue_forecast) exist in the table ds_forecast but not in the table ds_sales. You must create new build elements to cater for these columns. To do this:

Creating Build Elements The new elements are metrics that correspond to the columns units_forecast and revenue_forecast. To add the new build elements: 1

Expand the build 3_MultipleSource.

2

Select Elements and from the Build menu choose Insert Metric. The Metric Properties dialog box opens.

3

Into the Name box, type units_forecast.

4

Click OK. DecisionStream offers to add the new element to each delivery module in the build. Click OK.

5

In a similar manner, create a new metric element named ‘revenue_forecast’.

DecisionStream uses summation as the default aggregation function. You require this aggregation function. Hence, you do not need to set this explicitly.


Builds

Adding the New Data Source To add the new data source 1

Select the DataStream of 3_MultipleSource and from the Build menu choose Insert Data Source. The Data Source Properties dialog box opens. Into the Name box, type ForecastSource and then click OK.

2

Expand the new data source and select the SQL node.

3

Click Properties editor on the toolbar.

4

From the Database list, select Tutorial Sales and then click SQL Helper.

5

In the Database Objects pane, double-click Tutorial Sales and then control-drag ds_forecast to the SQL Query pane. The SQL Helper window should look like this:

6

Click OK in SQL Helper and the Data Source SQL dialog box.

7

Map the columns of the new data source to the appropriate build elements. To do this: a.

Select the Mapping node of ForecastSource and click Properties editor on the toolbar. The Data Source Mapping dialog box opens.

75

76

Builds


b.

In this dialog box, map each column to the appropriate build element by dragging the element to the column:

§ § § § §

Drag the period_no element to the quarter_no column Drag the state_cd element to the state_cd column Drag the product_cd element to the product_cd column Drag the units_forecast element to the units_forecast column Drag the revenue_forecast element to the revenue_forecast column.

The Data Source Mapping dialog box should look like this:

Mappings for data source ‘ForecastSource’

c.

Click OK.

Viewing the New Mappings DecisionStream provides a graphical view of the data source column mappings. To view this, select the DataStream of 3_MultipleSource. DecisionStream shows the mappings in the Build Visualization pane.


Builds

Mappings for the DataStream of build ‘3_MultipleSource’

Modifying the Properties of the period_no Dimension The table ds_forecast has no data at the Period level of the Fiscal hierarchy. Its lowest level is the fiscal quarter. Therefore, you must modify the dimension period_no to allow DecisionStream to accept source data at both the Period and Quarter levels. To do this: To modify the properties of period_no dimension: 1

Select the period_no element and click Properties editor on the toolbar. The Dimension Properties dialog box opens.

2

Display the Reference tab.

77

78

Builds


3

In the Input column, select the check box for the level Quarter. The dialog box should look like this:

4

Click OK.

Select the top node of the build. In the Build Visualization pane, the build should look like this:

Build ‘3_MultipleSource’ in the Build Visualization pane

DecisionStream provides a graphical view of the relationship between the dimension elements and their associated hierarchies. This view also shows at which hierarchy levels DecisionStream accepts and delivers data. To see this, select any element. DecisionStream shows the view in the Build Visualization pane.


Builds

Dimension Element and Hierarchy relationships, build ‘3_MultipleSource’

In the preceding illustration, you can see that the Fiscal hierarchy accepts data at the Quarter and Period levels; the other hierarchies accept data only at the lowest level. Because the build performs aggregation, it can output fact data at all hierarchy levels. However, you could modify this (in the properties dialog box of the corresponding dimension element) to output only summary data.

Testing the Build Before testing the build, save the catalog to disk by clicking Save current catalog on the toolbar. You can test the build by executing it in check only mode. To test the build: 1

Select the build and from the Build menu choose Execute Build. If you executed a previous build, then the Target Table Check dialog box opens. This dialog box reports inconsistencies in the target tables. Click Drop Tables and then click Yes to confirm this action.

2

The Execute Build dialog box opens.

3

From the Execution mode list, select Check Only.

4

Click OK. Whenever you execute a build, DecisionStream checks whether the target tables exist and have the correct structure. If it finds inconsistencies, DecisionStream prompts you to drop the target tables. The previous build created the target table ‘ds_sales_conso’. However, the structure of that table is incorrect for this build.

79

80

Builds


DecisionStream launches DataBuild in a DOS window. As the build progresses, DataBuild traces the progress and writes information to the screen. 5

The build completes. Press ENTER to close the DOS window.

Adding Inventory Information In this build, you add inventory information to a duplicate of the previous build. The new data source contains inventory information with each row giving the stock held of a particular product, at a particular location, at the start of a particular fiscal period. Before you can proceed, you should know something of aggregation exceptions and dimension datasets.

Aggregation Exceptions Assume that you want to know the total number of all items at a particular location or the total stock of a particular product for a given state or time zone. You obtain this by setting the aggregation method of the stock metric element to SUM. Doing this sets the aggregation method to SUM for all dimensions. Unfortunately, summing inventory data along a time dimension means little. For example, if the stock holding of a product is 100 in month one, 120 in month two, and 80 in month three, then the sum for the period is 300. However, this exceeds the stock holding at any time during the period. DecisionStream provides aggregation exceptions to deal with this type of situation. An aggregation exception instructs DecisionStream to deliver the first (or last) value of a particular metric when aggregating along a particular dimension. For example, using an aggregation exception, you can instruct DecisionStream to deliver the stock level for the first (or last) period in each quarter.

Dimension Domain Properties The dataset of a dimension defines the set from which each member draws its value. The domain of the dimension is the set of different values that exist within the dimension. It is a subset of the dataset.


Builds

81

Consider the period_no dimension. Its dataset is the set of periods, quarters, and years that lie within a contiguous fiscal time span. However, its domain may not include one or more fiscal periods. For example, assume that the dataset includes all of 1998. At the end of October 1998, the source data would not include data for November and December of 1998. Therefore, the dimension domain would not include those months. The following illustrations show the dataset and domain respectively for the latter half of that year.

Dataset (left) and Domain (right) for the latter half of 1998.

Similarly, if the company did not trade during a particular period (for example, if they were closed for vacation), then the source data (and hence the dimension domain) would not contain that period. When delivering aggregation exceptions, DecisionStream must be able to identify dataset that are missing from the domain. Unless it can do this, DecisionStream cannot determine the full range of each summary hierarchy member (for example, it the preceding example, DecisionStream could not determine that the range for Q4 was {Oct, Nov, Dec}). During build execution, DecisionStream constructs the domain of each dimension. Either it can construct the domain from the associated hierarchy, or it can construct the domain dynamically from the source data. The Domain tab of the Dimension Properties dialog box enables you to specify from where DecisionStream should obtain the domain of the element. The Domain Type column in this tab contains the options Dynamic Domain and Reference Domain.

Dynamic Domain DecisionStream builds the domain from the source data it acquires. Therefore, the domain may not contain all of the hierarchy dataset.

Reference Domain DecisionStream obtains the domain from the hierarchy that relates to the dimension. Therefore, the domain consists of all values that are present in the hierarchy.

82

Builds


If you set the domain type to Dynamic Domain, then DecisionStream cannot deliver an aggregation exception for that dimension. Therefore, you must always set the domain type to Reference Domain if you require an aggregation exception for the dimension.

Creating the Build Creating a Duplicate Build To create a duplicate build: 1

Select 3_MultipleSource and from the Build menu choose Duplicate. DecisionStream creates a duplicate build, named 3_MultipleSource:1.

2

Select the new build and click Properties editor on the toolbar. The Build Properties dialog box opens.

3

Change the name and the caption to 4_AddStock and then click OK.

Creating New Build Elements The new build elements cater for the inventory information. Each uses an aggregation exception to allow DecisionStream to deliver the stock value at the first (or last) period of each quarter and year. To create new build elements: 1

Expand the build 4_AddStock.

2

Select the Elements node and from the Build menu choose Insert Metric. The Metric Properties dialog box opens.

3

Into the Name box, type opening_stock.

4

Display the Aggregation tab and create the exception: a. Select the Exception check box. b. Select First and, from the adjacent list, select period_no.


Builds

83

The dialog box should look like this:

c.

Click OK. DecisionStream offers to add the new element to each delivery module in the build. Click OK.

This metric element represents the opening inventory level for each quarter, and year. 5

In a similar manner, create a metric element, named ‘closing_stock’, that has an aggregation exception that returns the last value with respect to the dimension period_no. The Aggregation tab for this metric element should look like this:

This metric element represents the closing inventory level for each quarter, and year.

84

Builds


Because DecisionStream cannot deliver an aggregation exception for a dimension element that has a domain type other than ‘Reference Domain’, you must now change the domain type of the element ‘period_no’.

Changing the Domain Type of Element period_no Changing the domain type of element period_no: 1

Select the element period_no and click Properties editor on the toolbar. The Dimension Properties dialog box opens.

2

Display the Domain tab. From the Domain Type list, select REFERENCE DOMAIN.

3

Click OK.

You must now add a data source to the build. From this data source, DecisionStream acquires the inventory information. DecisionStream can accept data from multiple databases (and database management systems). The databases need not reside on the same computer or run under the same operating system. However, when you execute the build, DecisionStream must be able to access all the databases that contribute to the build. In this example, the inventory data comes from the table ds_stock in the Tutorial Stock database.

Adding the New Data Source Adding the new data source: 1

Select the DataStream icon and from the Build menu choose Insert Data Source. The Data Source Properties dialog box opens.

2

Into the Name box, type StockSource and then click OK.

3

Expand the data source StockSource and select the SQL node.

4

Click Properties editor on the toolbar.

5

From the Database list, select Tutorial Stock and then click SQL Helper.

6

In the Database Objects pane, double-click Tutorial Stock and then control-drag ds_stock to the SQL Query pane.

7

Click OK in SQL Helper and in the Data Source SQL dialog box.


8

Builds

Map the columns of the new data source to the appropriate build elements. To do this: a. b.

Select the Mapping node of StockSource and click Properties editor on the toolbar. In this dialog box, map the columns to the appropriate build elements:

§ § § § §

Drag the period_no element to the period_no column Drag the state_cd element to the state_cd column Drag the product_cd element to the product_cd column Drag the opening_stock element to the stock_units column Right-drag the closing_stock element to the stock_units column, thus adding it to the existing mapping.

The Data Source Mapping dialog box should look like this:

c.

Click OK.

Two elements (opening_stock and closing_stock) map to the stock_units column. 9

Click Save Current Catalog to save your work.

85

86

Builds


Select the top node of the build. In the Build Visualization pane, the build should look like this:

Build ‘4_AddStock’ in the Build Visualization pane

Test the new build using check-only mode. To do this: 1

In the Build window, right-click the build and choose Execute Build from the shortcut menu.

2

If DecisionStream prompts you to drop the existing table ds_sales_conso: a. Click Drop Table b. Click Yes to confirm this action

3

From the Execution mode list, select Check Only.

4

Click OK.

Viewing the New Mappings To view a graphical representation of the mappings between the DataStream and the data sources, in the Build window, select the DataStream. DecisionStream shows the mappings in the Build Visualization pane.


Builds

87

Mappings for the DataStream of build ‘4_AddStock’

Partitioned Delivery DecisionStream s both horizontal and vertical partitioning of the delivered fact data. It uses two mechanisms to this. Its subscription mechanism s vertical partitioning (partitioning into column subsets). That is, each delivery module subscribes to certain build elements. DecisionStream delivers only those elements to which the delivery module subscribes. You met the concept of subscription when you added elements to previous builds. When DecisionStream added the new elements to the existing delivery modules, it caused those delivery modules to subscribe to the new elements.

88

Builds


DecisionStream s horizontal partitioning (partitioning into row subsets) via filters. Within each delivery module, you can specify up to one output filter and any number of level filters. Each level filter defines the hierarchical levels at which DecisionStream delivers the data via that delivery module. Thus, level filters enable you to create summary fact data tables at different hierarchy levels. In this example, you use level filters to partition the delivered data into three tables, one for each level of the Fiscal hierarchy.

Creating a Duplicate Build Create a duplicate build: 1

Select 4_AddStock and from the Build menu choose Duplicate. DecisionStream creates a new build named 4_AddStock:1.

2

Select the duplicate build and click Properties editor on the toolbar. In the Build Properties dialog box, change both the name and caption to 5_MultipleDelivery. Click OK.

3

The duplicate build has an unwanted delivery module that delivers all the data into a single table. Remove this by expanding the build, then the Delivery Modules node, and then the Fact Delivery Modules node. Select the delivery module ds_sales_conso and from the Build menu choose Delete. Click OK to confirm the action.

Adding the Delivery Modules You can add the required delivery modules by hand. However, the Build Wizard can help you to add additional delivery modules to an existing build. You can choose an alternative template when you do this. Thus, this enables you to configure a build using more than one template. In this example, you use the Build Wizard to add the required delivery modules. To add the delivery modules: 1

Click Run build wizard on the toolbar. The Build Wizard opens.

2

Select Add to an existing build and then select the build 5_MultipleDelivery.


Builds

3

From the Select the template to use for this build list, choose Relational Datamart. Under Should the build perform a full refresh, select Yes. Click Next.

4

From the Connection list, select Examples Datamart. Select the Deliver Fact Data check box, and clear both the Deliver Dimension Data and Deliver Metadata check boxes.

5

Click Review (adjacent to Deliver Fact Data). The Review Fact Delivery Properties dialog box opens. In the Partition Build group, select Use settings that I will define. This causes two additional tabs (Split Dimensions and Split Levels) to appear.

6

Define the dimension on which DecisionStream must partition the data. To do this: a. Display the Split Dimensions tab. b. Select period_no and clear both state_cd and product_cd.

7

Display the Naming tab. Establish the naming strategy for the delivered tables. To do this, change the entry in the Table Name/Prefix box to ds. The dialog box should look like this:

8

Click OK in this dialog box to return to the Build Wizard.

9

Click Finish.

89

90

Builds


In the Build window, select the build MultipleDelivery. The Build Visualization Pane should look like this:

Build ‘5_MultipleDelivery’ in the Build Visualization pane

Test the build using check-only mode.

Delivering Data to a Cognos BI Mart This example differs from the others in that it does not modify a previous example. It acquires data from a single source table, and delivers it (without aggregation) to a Cognos BI Mart. In addition to a Cognos Impromptu Catalog and a PowerPlay Transformer model, DecisionStream creates a relational data mart (star schema) with the fact and dimension data. It optimizes the PowerPlay Transformer model to analyze and report on this datamart. It also creates sample PowerPlay and Impromptu reports. From the PowerPlay reports, you can drill through to Impromptu to examine detailed data. During execution of this build, DecisionStream invokes both PowerPlay and Impromptu. Therefore, you must have these Cognos products installed on your computer to execute this build. To create this build: 1

Click Run build wizard on the toolbar. The Build Wizard opens with the Create a new build option and the Cognos BI Mart (Star) template selected. Under Should the build perform a full refresh, select Yes. Click Next

2

Into the Name box, type BIMartBuild. Click Next.


3

Builds

Identify the source for the build: a. From the Connection list, select the Tutorial Sales connection. Click Next. b. Select the check box adjacent to the table ds_sales. The Build Wizard should look like this:

Click Next. 4

The Build Wizard shows the default elements for the build. For this build, these are correctly defined. Click Next.

5

Associate each dimension element with the appropriate hierarchy by choosing the hierarchy from the Use Reference column:

§ § § § 6

For period_no, choose the Fiscal hierarchy For state_cd, choose the Location hierarchy For product_cd, choose the Product hierarchy Click Next.

Specify the data that this build delivers: a. From the Connection list, select Examples Datamart. b. Ensure that the Deliver Fact Data, Deliver Dimension Data, and Deliver Metadata check boxes are selected.

91

92

Builds


The Cognos BI Mart (Star) template automatically delivers metadata to both PowerPlay and Impromptu. If you do not have one of these products, you can choose not to generate metadata for that product. To do this: a. Click the Review button adjacent to Deliver Metadata. b. The Review Metadata Delivery opens. c. Clear the check box for the product you do not have. You can also use this dialog box to add metadata to other OLAP tools. To do this, select the check box that corresponds to the required format. 7

Click Finish to close the Build Wizard.

8

Execute the build in Normal mode.

In the Build Visualization pane, the build should look like this:

Build ‘BIMartBuild’ in the Build Visualization pane

Viewing the Results To view the results of executing this build, you must have both Cognos PowerPlay and Cognos Impromptu installed on your computer. In this part of the exercise, you drill down through the Location and Product dimensions in PowerPlay, and finally drill through to an Impromptu report.


Builds

93

To view the results of executing this build: 1

From the Tools menu, choose Browse Data Files. The DecisionStream data directory opens.

2

Locate the file BIMartBuild.mdc. This file is the PowerCube that DecisionStream created when you executed the build. Double-click this file.

3

The PowerCube opens in PowerPlay Explorer and should look like this:

PowerCube ‘BIMartBuild.mdc’ in PowerPlay Explorer

4

Drill down the Location and Product dimensions: a. On the PowerPlay Folder line, point to the Location folder and choose Eastern Standard Time from the fly-out menu. b. Point to the Product folder and choose Kitchen from the fly-out menu. The Folder line should now look like this:

PowerPlay Folder line

5

Drill through to an Impromptu report: a. In the PowerPlay report, select the cell at the intersection of FY97 and Florida. b. From the Explore menu, choose Drill Through. The Drill Through dialog box opens. c. From the Impromptu Reports folder, choose f_bimartbuild then click OK.

94

Builds


d.

This report opens in Impromptu:

Please explore the PowerCube in Cognos PowerPlay and drill through to Impromptu to see how easily you can access the data at any hierarchy level combination.

PowerPlay Dimensions When you executed the build BIMartBuild, DecisionStream delivered the Fiscal, Location, and Product hierarchies to PowerPlay and Impromptu. You can compare the PowerPlay dimensions with the DecisionStream hierarchies. To compare the PowerPlay dimensions with the DecisionStream hierarchies: 1

In PowerPlay, click Dimension Viewer on the toolbar. The PowerPlay window splits into two panes, the left of which contains a view of the dimensions.

2

In DecisionStream, restore the Reference window. Select the Fiscal hierarchy, from the Reference menu choose Hierarchy Explorer, and then click OK.

3

In both the PowerPlay Dimension View pane and the DecisionStream Hierarchy Explorer, expand the Fiscal hierarchy, the FY96 node, and the FY96 Q1 node.


Builds

Hierarchy ‘Fiscal’ in DecisionStream

95

Dimensions in PowerPlay

4

Compare the two views. Notice how they correlate.

5

Expand other nodes in both views and other hierarchies in DecisionStream Hierarchy Explorer, noting that the PowerPlay dimensions have the same structure as the DecisionStream hierarchies.

Delivering Data to Microsoft OLAP Services This example acquires data from a single source table, and delivers fact data and dimension data to the Examples Datamart. It delivers metadata to a Microsoft OLAP Services database. The metadata consists of a virtual cube for the build and a cube for the fact delivery. During execution of this build, DecisionStream invokes the Microsoft OLAP Services Client. Before you can successfully execute this build:

§ § §

You must have the Microsoft OLAP Services Client installed on your computer. Microsoft OLAP Services must contain a database that contains a DataSource definition that refers to the database into which DecisionStream delivers the fact and dimension tables. You must configure a DecisionStream connection for the Microsoft OLAP Services database.

96

Builds


To create the Microsoft OLAP Services DataSource: 1

Start the Microsoft OLAP Manager.

2

In the left pane, expand the OLAP servers folder.

3

On an appropriate server, create a database with the name ‘DS_Tutorial’. To do this: a. Right-click the server and choose New Database from the shortcut menu. b.

Enter the name DS_Tutorial and then click OK.

4

Expand the DS_Tutorial database node, then the Library node.

5

Create the required data source. To do this: a. Right-click the Data Sources node b. In the Data Link Properties dialog box, select the connection type for the Examples Datamart (ExampleMart.mdb) database. c. Click Next and enter the configuration information for the Examples Datamart database. The left pane of the OLAP Manager should look like this:

To create a connection to the Microsoft OLAP Services database: In DecisionStream Designer: 1

Click the Edit connection definitions toolbar button. The Connections dialog box opens.

2

Click New. The Connection Properties dialog box opens.

3

Into the Alias box, type MSOLAP.

4

Specify the Microsoft Olap Services database. To do this: a. Display the Connection Details tab. b. In the left pane, select the MS OLAP Services connection type. c. From the Server list, select the server on which you created the DS_Tutorial database.


Builds

d. 5

97

From the Database list, select DS_Tutorial.

Create a mapping between the Examples Datamart DecisionStream connection and the Microsoft OLAP Services data source that you created earlier. To do this: a. Click the Edit Mapping button. The MSOLAP DataSource Mapping dialog box opens. b. In the lower-left pane, double-click the Examples Datamart connection. This connection is copied to the upper pane. c. In the lower-right pane, double-click the DataSource entry. This is copied to the upper pane, completing the required mapping. The MSOLAP DataSource Mapping dialog box should look like this:

Click OK. The name of the DataSource comes from Microsoft OLAP Services. Thus, it may differ from that shown in the preceding illustration. Having created the Microsoft OLAP Services data source and the DecisionStream connection, you can use them with any number of DecisionStream builds. This example delivers the fact data, dimension data, and metadata for the Tutorial Sales database.

98

Builds


To create this build: 1

Click the Run build wizard toolbar button. The Build Wizard opens with the Create a new build option selected. From the Select the template to use for this build list, choose Microsoft OLAP Services Datamart. Under Should the build perform a full refresh, select Yes. Click Next

2

Into the Name box, type MSOLAPBuild. Click Next.

3

Identify the source for the build: a. From the Connection list, select the Tutorial Sales connection. Click Next. b. Select the check box adjacent to the table ds_sales. The Build Wizard should look like this:

Click Next. 4

The Build Wizard shows the default elements for the build. For this build, these are correctly defined. Click Next.

5

Associate each dimension element with the appropriate hierarchy by choosing the hierarchy from the Use Reference column:

§ § §

For period_no, choose the Fiscal hierarchy For state_cd, choose the Location hierarchy For product_cd, choose the Product hierarchy

Click Next.


6

Builds

Specify the data that this build delivers: a. From the Connection list, select Examples Datamart. b. Ensure that the Deliver Fact Data, Deliver Dimension Data, and Deliver Metadata check boxes are selected. c. Configure the build to deliver fact data into a single table. To do this: i.

Click the Review button next to the Deliver Fact Data check box. The Review Fact Delivery Properties dialog box opens.

ii.

Display the Generation tab. In the Partition Build group, select Use this strategy and choose All data into a single table.

The Review Fact Delivery Properties dialog box should look like this:

Fact Delivery Generation properties

Click OK. 7

99

Click Finish to close the Build Wizard. The Microsoft OLAP Services Datamart template automatically delivers metadata to the primary connection to Microsoft OLAP Services. This is the first connection that you create in the catalog. If you want DecisionStream to deliver the metadata to a different Microsoft OLAP Services database, then you must edit the build by hand. Please refer to the ’s Guide for information on editing builds.

100

Builds


In the Build Visualization pane, the build should look like this:

Build ‘MSOLAPBuild’ in the Build Visualization pane

8

Execute the build in Normal mode.

Viewing the Results You can view the results in the Microsoft OLAP Manager (shown below). You can also view the results in a suitable alternative OLAP tool.

The Delivered Cube in Microsoft OLAP Manager

Glossary

Aggregation The process of taking data at one level of detail and summarizing it to a lower level of detail. The process can occur in a number of levels simultaneously (multi-level aggregation) or even in a number of dimensions simultaneously (multi-dimensional aggregation). Alias A shorthand identifier for a connection to a database. ASCII A common encoding for character values on UNIX and Windows NT/95 systems. Break The identification of a change in the dimension sequence. It is used to improve Engine performance and reduce memory usage. Build An DecisionStream unit of work that goes through the steps of Acquisition, Transformation, and Delivery. Build Element One of a Build's dimensions, metrics, derivations, or attributes. Calculate at Source The calculation method used to obtain source derivations. A source derivation is calculated for detail data and aggregated as though it were a metric. It is the equivalent of performing a calculation in the SQL query and then treating the resulting column as a metric.

102

Glossary


Calculation Either the method used to generate a derivation, or the method used for aggregation of a metric. Caption A piece of information that is associated with a member id or name. This is often used (for display and presentation purposes) in preference to the id or name. Catalog A repository for storing DecisionStream Build, Reference, and Connection specifications. Check Only A method of executing a Build that results in the entire Build process being completed apart from the final delivery of data. It is generally used during development of a new Build. Command Line The name of a command, together with its associated arguments. Component A build, dimension build, collection, hierarchy, lookup, or connection in a catalog. Connection All the information that is required to make a connection to a database. The actual information that is required will vary based on the database and the connection method. DecisionStream collects this information and associates it with a database alias for ease of reference. Connection Method The method of connection to a source of data. The method, named from the driver that the connection uses, is different for each database type. Data Mart A database that holds data for analysis. It usually implies that the data and its organization is of limited scope or is targeted at a particular group of s.


Glossary

103

Data Model A system consisting of fact data and metadata. Data Source Somewhere from which the DecisionStream can acquire data. Data Type Any piece of information that DecisionStream processes has a data type. Often, DecisionStream's default data type is acceptable. However, you may sometimes need to explicitly state the data type to use. DecisionStream's data types are: CHAR - A piece of text INTEGER - A positive or negative whole number DOUBLE - A positive or negative floating-point number DATE - A date and time BOOLEAN - A value that can either be TRUE or FALSE. Data Warehouse A database that is used to hold data for analysis. The data may be accessed directly by s or it may be used to feed data marts. Generally, the data warehouse is used as the source of reporting data for the whole enterprise. Database Storage of data with software to manage access to the data. Dataset The possible set of items that can exist at a hierarchy level. Each member of the dataset is a dataset attribute. Dataset Attribute Dataset attributes on a hierarchy level collectively make up the dataset. Each level data select can have columns mapped onto dataset attributes. A datatset attribute can have special significance, for example, when it represents the holder for the id of a hierarchy member. In this case it is explicitly tagged as having special significance. Tags are available for member ids ($ID), captions ($CAPTION), and parents ($PARENT).

104

Glossary


This mechanism gives a layer of abstraction that allows DecisionStream to build a single hierarchical level from multiple sources. Date Hierarchy The Designer generally builds hierarchies with reference to data in relational tables. Often, this is not possible for dates because the data is frequently not held in relational tables. The Designer provides a wizard that you can use to generate a hierarchy of dates that is based on a date range that you specify. DBMS Database Management System. Delivery The operation that results in data being delivered to target systems via the delivery modules. Delivery Module A software module (in Windows, this is a DLL) that provides for delivery of data and metadata to a variety of target system types (for example, relational databases). Derivation A metric that the DecisionStream derives (that is, calculates) rather than obtains directly from the data source. Designer DecisionStream Designer is the Windows 95/98/NT software that you use to produce Build specifications and Dimensional Frameworks. Dimension A data element that is fundamental to data organization and provides context to numeric items of data. A dimension consists of discrete values that represent the keys of the data. Common examples are time, product, and location. Dimension Build An DecisionStream build process that delivers only dimension data.


Glossary

105

Dimensional Framework A repository for reference structures (hierarchies and lookups) that promotes the reuse of these structures. Domain The set of that exist in a dimension. Domain Type The domain of a dimension can be one of two types. It is the domain that is used to deliver dimension data at the end of a build. The two types are: Dynamic - the domain is build up from data sources as rows are processed. Reference - the reference object associated with the dimension is taken as the domain. Drill To move between the levels of a hierarchy by following parent-child links. Drilling down follows the link from parent to child. Drilling up follows the link from child to parent. Driver A software module (a DLL in Windows) that provides for a certain type of database access (for example, Oracle SQL*Net, ODBC). Dynamic of hierarchies or lookups that are defined by reference to tables in database. EBCDIC The character set and method of encoding used on IBM machines. Element The set of a Build's dimensions, metrics, derivations, and attributes. Engine The DecisionStream component that actually carries out the transformations.

106

Glossary


Environment A value that indicates the execution circumstances of the DecisionStream Engine and, therefore, provides information about the method of connection to source and target database. Exception A dimension that has a different aggregation method for a metric. Filter DecisionStream tests, against a set of filters, each data row that it es to a delivery module. The test results establish whether DecisionStream should deliver the data row. Foster Parent An artificially introduced member that acts as a parent for that either have no defined parent or whose defined parent cannot be found at the next highest hierarchy level. Function A routine that processes a set of parameters in order to derive a new value. Hierarchy A structure that holds related organized into levels. Hierarchy Explorer An DecisionStream application that builds a hierarchy or lookup from its statically and dynamically defined . It provides facilities to explore the data of the hierarchy. id The value that uniquely identifies a member in data. For example, the id for a product may be its product-code. Key The set of columns in a table that identify set of rows within the table.


Glossary

107

Level Hierarchies are made up of levels with a level being used to hold a set of . at a level are related to the at the nexthighest level (their 'parents') and at the next-lowest level (their 'children'). Level Filter A filter that is able to test whether a row of data has a certain combination of levels and/or . Log File A file that is used to redirect output from the Engine. Lookup A simple reference object that consists of that are not arranged into hierarchical levels. You can consider a lookup to be a one-level hierarchy. Mapping The relationship between columns (from an SQL query) and the DecisionStream objects to which they relate. DecisionStream has mappings that relate data sources to build elements and level data selects to dataset attributes. Member A node in a hierarchy. Metadata Data that describes the delivered data. Typically, metadata defines the data structure and the domain of each dimension. It can also provide aliases that reporting tools can use to provide meaningful descriptions. Metadata Delivery The delivery of metadata based on the domain of dimensions. Metric A value, frequently numeric, that holds a piece of information for analysis. For example, units, revenue, cost, etc. Model A system that consists of fact data and metadata.

108

Glossary


Module A delivery module, that is, a software module (in Windows, this is a DLL) that provides for delivery of data and metadata to a variety of target system types (for example, relational databases). Multi-dimensional Any system for which the dimension is the fundamental basis of data organization. Multi-dimensional Aggregation The process of taking data at one level of detail and simultaneously summarizing it across a number of dimensions. Name The identifier for an DecisionStream object. For example, a level. Normalized data Data that is structured to avoid data anomalies. Typically, operational data is normalized. Notes Optional, text-based information that can be saved with DecisionStream objects. ODBC A connectivity method that provides a common interface to a wide variety of database products. OLAP On-line analytical processing. Output filter A filter that can be specified in a delivery module to restrict delivery of rows to the target system. The output filter takes any expression that evaluates to TRUE or FALSE. Output Level A level of a hierarchy at which DecisionStream will output data. Output data may be generated by aggregation.


Glossary

109

Page A unit of memory that DecisionStream uses to hold intermediate data. Under certain circumstances, DecisionStream may move pages of data to disk and retrieve them to main memory when next needed. This process is called 'paging'. Parametric Interface A human-computer interface, consisting of on-screen forms, where the enters values into pre-defined boxes. Parent The member on the next-highest level of a hierarchy that is related to the member under consideration. Partitioned Delivery Delivery of a data stream to a number of targets where the data stream is divided (by defined criteria) between the targets. Reference Attribute Reference attributes on a hierarchy level collectively make up the dataset. Each level data select can have columns mapped onto dataset attributes. A reference attribute can have special significance, for example, when it represents the holder for the id of a hierarchy member. In this case it is explicitly tagged as having special significance. Tags are available for member ids ($ID), captions ($CAPTION), and parents ($PARENT). Reference Data Semi-static data that is used to provide context for other data within a relational database. For example, a product master will contain a list of products with which the company deals. The product master may also contain ing information, such as product characteristics. Referential Integrity In a relational database, the posting of foreign keys represents the relationship between one table and another. Such a relationship exhibits referential integrity if the set of foreign key values is a subset of the referenced primary key values. That is, there are no 'orphaned' foreign keys. Roll-up To summarize data, creating summary records at a higher hierarchical level.

110

Glossary


Source A data source. That is, somewhere from which the Engine or the Designer can acquire data. SQL Structured Query Language. An almost universal query language that combines data definition with data manipulation. SQL Helper A link that allows you to use SQLTerm to produce and test SQL queries. SQL Query Any valid SQL statement in the dialect of the target database. This may be either data definition (for example, CREATE TABLE) or data manipulation (for example, SELECT). SQLTerm A terminal monitor application that provides a common interface to the various relational database products that DecisionStream s. SQLTXT An implementation of SQL over text files. Star Schema See metadata schema. Static of hierarchies or lookups that are defined as part of the specification, and not by reference to tables in a database. Target Any database or other system to which DecisionStream delivers data. Transformation The re-expression of a stream of data in some different format. Typically the merging and aggregation of source data to provide delivery elements. Type See data type.


Glossary

Unmatched Member A member in a dimension that does not match with a member in the associated hierarchy.

111

112

Glossary


Index A adding a data source to builds, 78, 89 aggregation, 66 Aggregation Advisor, 13 aggregation exception, 85 anatomy (of a build), 73 associating dimension with hierarchy, 68 attributes, 44, 65

B BI Mart, 95 Break/Sparsity Advisor, 13 build elements, 76 creating, 79, 87 Build Tree, 73 Build Visualization pane, 13 Build window, 12 Build Wizard, 13, 60 builds, 18, 59 adding a data source, 78 adding inventory information, 85 attributes, 65 copying a table, 61 data source, 62 delivering data to a Cognos BI Mart, 95 dimensions, 65 duplicating, 78, 87, 93 elements, 65 executing, 63 metrics, 65 multiple data sources, 77 partitioned delivery, 92 simple build, 66 templates, 61 testing, 63

C catalog, 9 catalogs

creating, 25 changing the domain type, 89 components, 9 concepts, 15 connecting to reference database, 28, 30 to sales database, 30 to stock database, 30 connections, 16, 27 Connections dialog box, 12 constructing fiscal hierarchy, 40 location hierarchy, 53 product hierarchy, 48 conventions, typographical, vii copying tables, 61 creating build elements, 79, 87 catalog, 25 data source, 23 hierarchy levels, 41

D data connecting to, 27 examining, 38 reference, 28 transformation, 18 data delivery, 19 data mart, 30 data source, 16 adding to a build, 79 adding to build, 89 adding to builds, 78 creating, 23 mapping, 80, 90 databases reference, 28, 30 sales, 30 stock, 30 DataSource (Microsoft OLAP Services), 101 DataStream, 45, 75 Literals node, 45

114

Index

Mapping node, 45 SQL node, 45 Date Hierarchy Wizard, 13 decision , 19 delivering data, 19 delivering data to Cognos BI Mart, 95 delivery modules, 77 derivations, 66 adding, 69 dimension, 17 Dimension Build Wizard, 13, 18 dimension builds, 18 Dimensional Framework, 17 dimensions, 33, 65 associating with hierarchy, 68 domain properties, 85 modifying, 70 modifying properties, 82 domain changing type, 89 dynamic, 86 properties, 85 reference, 87 duplicating builds, 78 dynamic domain, 86

E elements, 65 attributes, 65 creating, 79 derivations, 66 dimensions, 65 metrics, 65 modifying dimension properties, 82 elements, build, 76 examining the data, 38 Examples Catalog, 25 exception, aggregation, 85 executing builds, 63 execution log, 71 exercises, practical, 21

F fiscal hierarchy, 38 fostering, 51

G Glossary, 107


H hierarchies, 17, 35 associating with dimensions, 68 attributes, 44 caption attribute property, 57 constructing fiscal, 40 constructing location, 53 constructing product, 48 creating levels, 41 dataset, 43 fiscal, 38 from multiple tables, 53 from the columns of a table, 38 from the rows of a table, 47 id attribute property, 57 levels, 43 location, 53 parent attribute property, 57 product, 47 static member, 52 surrogate attribute property, 57 viewing, 42, 50 Hierarchy Explorer, 42, 43, 50 Hierarchy Wizard, 13

I interface, DecisionStream Designer, 11

L levels, 43 adding to a hierarchy, 41 attributes, 44 Literals node, 45 location hierarchy, 53 log files, 71

M mapping data source, 90 data source to build elements, 80 viewing, 81, 91 Mapping node, 45 metadata, 19 metrics, 65 Microsoft OLAP Services, 100 modifying dimension properties, 82


O ODBC data source creating, 23 ODBC driver, 22 OLTP, 19 operating systems, 10

P partitioned delivery, 92 PowerPlay dimensions, 99 practical exercises, 21 procedures, 76 product hierarchy, 47 Properties dialog box, 12

R reference data, 28 reference database, 28, 30 reference domain, 87 Reference window, 12

S sales database, 30 saving data, 42, 50 snowflake schema, 53 split dimension, 94

Index

SQL, 17 SQL Helper, 80 SQL node, 45 SQLTerm, 12, 17, 38, 71 star schema, 40 stock database, 30 storing data, 42, 50 surrogate, 68

T table copying, 61 target data mart, 18 templates, 61 testing builds, 63 toolbar, 12 transforming data, 18 typographical conventions, vii

V viewing delivered data, 71 execution log, 71 mappings, 81

Z zoom, 13

115

116

Index


Tutorial Decision Stream Cognos 5q7048

Overview 4q3b3c

More details 26j3b

Related Documents 171j1w

Tutorial Decision Stream Cognos 5q7048

Cognos Cube Tutorial 2n5v34

Tutorial Super Decision Software 5s1t3t

Cognos Impromptu 5t1756

Decision rd6h

Cognos Dispatcher 2y1j2h

More Documents from "Fer" 2g701x

2c82t

Tutorial Decision Stream Cognos 5q7048

Educacion Presencial Vs Educacion Virtual 63o2i

Capa - Trabalho Barroco 5yt6s

Flyer Utn Daad 5w3m4f

Consecuencias De La Guerra De La Triple Alianza 132c3m