Home

	Expand Search

WarehouseArchitect - User's Guide

Part 3 WarehouseArchitect Model

Chapter 17: Generation for OLAP Systems

Chapter 17
Generation for OLAP Systems

About this chapterThis chapter explains how to generate for Online Analytical Processing (OLAP) systems.

What is an OLAP system?

To make informed decisions, decision makers need to analyze data from a number of different perspectives (business dimensions). OLAP systems make it possible to formulate queries using business dimensions. For example, in a query that requests sales by product, or sales by region, product and region are business dimensions.

What is a CUBE?A cube is a representation of multidimensional database data in a dimensional model. The cells of a cube contain data values The intersection of one member on each dimension represents a particular data value.

What is a MOLAP?A MOLAP is a multidimensional OLAP. It relies on precomputed cubes of data to speed responses to queries.

What is a ROLAP?A ROLAP is a Relational OLAP that enables a decision maker to assemble into a cube a subset of related data points that have been stored in a relational database.

Choosing a target OLAP engine

Interfacing with OLAP enginesWarehouseArchitect defines and presents information in a form expected by OLAP engines. The interface between WarehouseArchitect and OLAP engines ensures that the multidimensional database design in WarehouseArchitect is correctly implemented in the target OLAP engine.

The data files created by the rebuild and generate cube function are used by Powerplay and TM1. There is no direct interface between PowerDesigner and these MOLAP tools.

You can change the target OLAP engine into which you generate data.

OLAP engine toolsWarehouseArchitect supports several OLAP engine tools. Cube generation from the WAM creates CSV or TXT flat files containing data for use by MOLAP engine.

OLAP tool	Type	Requires cube generation	Generates structure from WAM interface
Express	MOLAP	(	--
Powerplay	MOLAP	(	--
Essbase	MOLAP	(	(
DSS Agent	ROLAP	--	(
Impromptu	Query and reporting tool	--	( *
PowerDimensions	ROLAP	--	( *
TMI	MOLAP	(	--

* Requires plug-in.

ODF and EXA filesThe OLAP engine dropdown listbox only displays OLAP engines for which there is an OLAP definition file (ODF). Some ODF files are associated with an extended attribute file (EXA). The EXA file is loaded automatically when you select the associated OLAP engine.

• To choose a target OLAP engine:
  1. Select WAM-->Change OLAP Engine and DSS tools.

     A confirmation box appears.

  2. Click OK.

     The Choose the Target OLAP Engine dialog box appears.

     

  3. Select the OLAP engine tool.

  4. Click OK.

Rebuilding and generating cubes

ROLAP tools do not require data files, you generate from the cube rebuild and generate functions.

Rebuilding a cube

Rebuilding a cube creates a query and inserts it into the SQL Cube box of the fact property sheet. You can view the updated fact property sheet from the multidimensional hierarchy or from the List of Facts dialog box.

You can select only one attribute per dimension to rebuild.

• To rebuild a cube:
  1. Select WAM-->Rebuild Cubes.

     The Rebuild cube for OLAP engines window appears.

     

  2. Expand the tree view to show all attributes and metrics.

  3. Click Rebuild Cube.

     The Messages window appears.

     

  4. Click OK.

Generating a cube

Generating a cube executes the query created by the cube rebuild and places the query results into CSV or TXT files. The filenames include the name of the fact table that you generate in the cube.

Generation optionsGeneration options indicate the format of OLAP scripts.

Option	Result of selection
Uppercase	Script contains all uppercase characters
Lowercase	Script contains all lowercase characters
No accent	Non-accented characters replace non-accented characters in script
Codes	Script refers to objects by their codes
Names	Script refers to objects by their names
Windows (ANSI)	Script uses ANSI character set
DOS (OEM)	Script uses DOS character set

• To generate a cube:
  1. Select WAM-->Generate Cubes.

     The Generate cube for OLAP engines dialog box appears.

     

  2. Select the All Facts radio button to convert all facts.

     or

     Select the List radio button, click the Question Mark button, select facts, and click OK.

  3. Click on the Options tab.

     

  4. Select options.

  5. Click Generate Cube.

     
     

     Connecting to a data source If you are not already connected to a data source, the ODBC connection dialog box appears. You must connect to the data source via an ODBC driver.

     
     

     

  6. Click Connect.

     The Messages window appears.

     

  7. Click OK.

Generating for PowerDimensions

The PowerDimensions plug-in is installed by the WarehouseArchitect Setup program. You access the plug-in from the WAM menu in the WarehouseArchitect interface. Using the plug-in interface, you can generate the following types of objects:

PowerDesigner object	PowerDimensions object
Table	Table (imported from data source)
Reference	Join path
Model	Model
Dimension	Dimension
Dimension hierarchy	Hierarchy
Attribute	Level
Metric	Measure

Importing tablesStrictly speaking, tables are not generated from PowerDesigner. When you generate a schema with the PowerDimensions plug-in, the table codes in PowerDesigner are passed along to PowerDimensions.

PowerDimensions uses these codes to indicate the data source tables it needs to build a schema. Therefore the data source table codes must match the table codes in the PowerDesigner data model.

You can ensure matching table codes by generating the data source database from the PowerDesigner model, or by reverse engineering the PowerDesigner model from the data source database.

Unsupported objectsIn PowerDimensions, hierarchies are not supported for facts or attributes. Dimension hierarchies can only be created under a dimension. A level can only be created under an hierarchy.

Facts in PowerDesigner are not generated as PowerDimensions objects, but any metrics they contain are generated as PowerDimensions measures.

Login attributesThe PowerDimensions plug-in requires you to specify the following login options:

Login attributes	Description
User Name	PowerDimensions server connection user name. The default user name is superuser
Password	Password of the user. No password is required for the superuser login

Schema generation optionsPowerDimensions builds a schema from tables in the data source database. You can build a schema manually in PowerDimensions, or you can generate it from the PowerDesigner data model.

Database schema options	Description
Generate Schema	Generates the PowerDimensions database schema corresponding to the WAM
Database Server	Name of the database server
Database Type	Name of the database driver. You can select any driver supported by PowerDimensions*
Database Name	Name of the database. This name must be the same as the ODBC data source
User Name	Name of the database user as defined in the ODBC interface
Password	Password of the database as defined in the ODBC interface

* If you are generating PowerDimensions for Sybase Adaptive Server IQ, you must select either ODBC (Generic) or ODBC (Sybase SQL Anywhere) as Database type.

Model optionsIf you select the Generate Schema and the Generate Model options from the PowerDimensions plug-in interface, the schema is built before the model is generated. If you do not select the Generate Schema option, the schema must already exist in PowerDimensions before you can generate the model.

Model option	Description
Generate Model	Generates the multi-dimension model in PowerDimensions
Build Hierarchy	Generates the data elements for the dimension

Extended attributesInformation supported by PowerDimensions, but not supported by PowerDesigner, can be generated via the extended attribute mechanism. Values you assign to extended attributes are generated at the same time you generate a model to PowerDimensions.

For information on PowerDimensions extended attributes, see the section PowerDimensions extended attributes

• To generate PowerDimensions objects:
  1. Make sure that your PowerDimensions server is running.

     
     

     Unlock the schema and close the model If a schema is locked in PowerDimensions, you cannot generate PowerDimensions objects. If you are generating a model that already exists in PowerDimensions, you must close the model in PowerDimensions before generating it again from PowerDesigner.

     
     

  2. Select WAM-->Sybase-->Generate PowerDimensions Repository.

     The PowerDimensions Generation Options dialog box appears.

     

  3. Type the User Name and Password in the PowerDimensions Login group box.

  4. Select database schema options.

  5. Select model options.

  6. Click OK.

Generating for Oracle Express

You can generate 3 different types of file from the WAM to use with Oracle Express:

• data files
• a structure file
• a program file

If you generate all files to the same directory, the program file can automatically load the data files that you create with cube generation.

You need to change the target OLAP engine to Express.

• To generate for Oracle Express:
  1. Select WAM-->OLAP Engines and DSS Tools-->Oracle-->Generate Express Database.

     The Generate Express Database window appears.

     

  2. Select Generation Parameters options.

  3. Type a destination directory in the Directory box.

  4. Type a name for the structure file in the Structure File box.

  5. Type a name for the program file in the Program File box.

  6. Expand nodes to display the hierarchical structure.

  7. Select or clear checkboxes.

  8. Click the Options button.

     The Generation Options for OLAP Engine dialog box appears.

  9. Select generation options.

  10. Click OK.

  11. Click the Generate button.

Generating for Essbase

Extended attributesWhen you change target OLAP engine to Essbase, default extended attributes are loaded in the WAM. You can view these attributes in the List of Attributes dialog box for facts, dimensions, and so on.

You must install Essbase on your local machine or copy the Essbase DLL files to your local machine to be able to access an Essbase server.

You need to change the target OLAP engine to Essbase.

• To generate an Essbase interface:
  1. Select WAM-->OLAP Engines and DSS Tools-->Arborsoftware-->Generate Essbase Application.

     The Essbase Application Generation window appears.

     

  2. Expand nodes to display the hierarchical structure.

  3. Select or clear checkboxes.

  4. Click the Options button.

     The Generation Options for OLAP Engine dialog box appears.

  5. Select generation options.

  6. Click OK.

  7. Click the Generate Outline button.

     
     

     Connecting to a data source If you are not already connected to a data source, the ODBC connection dialog box appears. You must connect to the data source via an ODBC driver.

     
     The Messages window indicates the name of the structure file you generated.

  8. Click OK.

Generating for DSS Agent

You can generate:

• scripts
• metadata

Generating a script for DSS Agent

You can generate a script file that you can run in your DSS Agent interface environment. You must first generate a project in DSS.

The script you generate inserts SQL queries into the structure metadata database. These queries can then be used by DSS Agent to extract data from the data source.

You can generate for DSS without changing the target OLAP engine.

• To generate a script for DSS Agent:
  1. Select WAM-->OLAP Engines and DSS Tools-->Microstrategy-->Generate DSS Agent Metadata.

     The Generate Metadata for OLAP Engine window appears. It shows the multidimensional model of the current WAM.

     

  2. Type a filename for the script file in the File Name box.

  3. Type a destination directory for the script file in the Directory box.

  4. Type a destination database for the script file in the Database Name box.

  5. Expand nodes to display the hierarchical structure.

  6. Select or clear checkboxes.

  7. Click the Options button.

     The Generation Options for OLAP Engine dialog box appears.

  8. Select generation options.

  9. Click OK.

  10. Click the Generate Script button.

      A standard file selection dialog box appears.

  11. Select an existing DSS Agent project.

      The project must exist in DSS Agent.

  12. Click OK.

      A message box shows the progress of the generation process.

  13. Click OK.

Generating metadata for DSS Agent

Metadata refers to the multidimensional objects that OLAP engines use to formulate queries of data warehouses. The metadata has the same business dimensions defined in the multidimensional model associated with the WAM.

You can generate metadata directly in the OLAP database.

• To generate metadata for DSS Agent:
  1. Select WAM-->OLAP Engines and DSS Tools-->Microstrategy-->Generate DSS Agent Metadata.

     The Generate Metadata for OLAP Engine window appears. It shows the multidimensional model of the current WAM.

     

  2. Type a destination filename in the File Name box.

  3. Type a destination directory in the Directory box.

  4. Expand nodes to display the hierarchical structure.

  5. Select or clear checkboxes.

     or

     Click the Select All button to select all checkboxes.

     or

     Click the Unselect All button to clear all checkboxes.

  6. Click the Options button.

     The Generation Options for OLAP Engine dialog box appears.

  7. Select generation options.

  8. Click OK.

  9. Click the Create Metadata button.

     
     

     Connecting to a data source If you are not already connected to a data source, the ODBC connection dialog box appears. You must connect to the data source via an ODBC driver.

     
     The Messages window indicates the name of the structure file you generated.

  10. Click OK.

Generating for Cognos Impromptu

You must install the Impromptu plug-in from the WarehouseArchitect Setup program for the plug-in to work properly. You generate a structure file from the WAM plug-in interface that you can use in Impromptu to obtain your query results.

From Impromptu you must first create a logical database name for a data source. You must also close the database catalog in Impromptu before you generate a catalog file from the WAM.

Uninstalling the Impromptu plug-in You must uninstall the Impromptu plugin before you uninstall WarehouseArchitect. To uninstall the Impromptu plugin, you must run PDOBJECT.EXE with /unregserver. You can do this from DOS or from the Start bar Run window.

• To generate Cognos Impromptu interface:
  1. Select WAM-->OLAP Engines and DSS Tools-->Cognos-->Impromptu generation.

     The Cognos Impromptu Generation window appears.

     A second dialog box asks for the logical database name for Impromptu.

     

  2. Select the catalog name.

  3. Select the database name.

     This is the logical database name. If the catalog name already exists in the database, a message box asks if you want to replace it.

  4. Click OK.

     

     The Cognos Impromptu Generation dialog box displays the name of the created CAT file.

  5. Select File-->Exit.

OLAP extended attributes

PrefixesEach extended attribute has a prefix, automatically added to it when it is created. The assigned prefix corresponds to the target OLAP engine that is currently selected.

If you access an extended attribute list from a property sheet you will not see the prefix. You can view the complete list of extended attributes, with their prefixes, from the Dictionary-->Extended Attributes-->List of Attributes menu in WarehouseArchitect.

The prefixes automatically added to an attribute are:

OLAP tool	Prefix
Essbase	ess_
Express	exp_
DSS Agent	dss_
Impromptu	imp_
PowerDimensions	Prefix not applied

Assigning values to OLAP extended attributes

You can assign values to extended attributes in the Multidimensional Hierarchy window from the property sheet of a fact, dimension, hierarchy, attribute, or metric. You can also create new extended attributes.

• To assign values to OLAP extended attributes:
  1. Select WAM-->Multidimensional Hierarchy.

     The Multidimensional Hierarchy window appears.

  2. Right-click a fact or other tree view object.

     A context menu appears.

  3. Select Properties.

     The object properties dialog box appears.

  4. Click the Extended button.

     The Extended Attributes Values dialog box appears.

  5. Click the Attributes button.

     The Extended Attribute Definition dialog box appears.

     

  6. Select an OLAP extended attribute.

  7. Type a value in the Value box.

     or

     Click the button, if it is active, and select a constant value.

     
     

     Creating an extended attribute You can click the New button to create an extended attribute. You select the data type for extended attributes from the Type dropdown listbox, and you assign a value in the Value box.

     
     

  8. Click OK in each of the dialog boxes.

PowerDimensions extended attributes

This section lists extended attributes with their descriptions and data types for Sybase PowerDimensions generation. Extended attributes are only provided for dimension attributes that are generated as levels in PowerDimensions.

Extended attribute	Data type	Description
AliasColumn	String	Column name for alias
KeyColumns	String	List of key columns separated by a comma
SortColumn	String	Column name for sort

Example 1In cases where you want to display a month by name, but sort it by number, you can assign Month_No as the value for the SortColumns extended attribute. Both Month_No and Month_Name columns must exist in the table you are generating.

Example 2You can also generate a list of key columns to a PowerDimensions level using the KeyColumns extended attribute. For instance, you might generate Year, Month_No to a Month_Name level, or Year, Quarter to a Quarter level.

Essbase extended attributes

This section lists extended attributes with their descriptions, data types, and default values that are necessary for Arbor Essbase OLAP generation.

Fact extended attributes

Extended attribute	Data type	Description and default
ess_application	String	Application name No default
ess_database	String	Database name No default
ess_login	Boolean	Login name Default=FALSE
ess_password	String	Password No default
ess_server	String	Server name No default
ess_username	String	User name No default

Dimension extended attributes

Extended attribute	Data type	Description and default
ess_category	Integer	Dimension category Default=ess_CAT_NONE
ess_share	Integer	Share option Default=ess_SHARE_DATA
ess_storage	Integer	Dimension storage type Default=ess_DIMTYPE_DENSE

Dimension Hierarchy extended attributes

Extended attribute	Data type	Description and default
ess_category	Integer	Dimension category Default=ess_CAT_NONE
ess_share	Integer	Share option Default=ess_SHARE_DATA
ess_storage	Integer	Dimension storage type Default=ess_DIMTYPE_DENSE

Metric extended attributes

Extended attribute	Data type	Description and default
ess_consolidation	Integer	Unary consolidation type Default=ess_UCALC_ADD
ess_expense	Boolean	Expenditure Default=ess_FALSE
ess_skip	Integer	Time balance skip option Default=ess_SKIP_NONE
ess_timebalance	Integer	Time balance option Default=ess_TIMEBAL_NONE
ess_twopass	Boolean	Calculation order Default=ess_FALSE