OLAP Cubes can be created from fact, fact view or aggregate objects. A single cube can contain dates from multiple source star schemas, each defined with a measure group. An OLAP Cube consists of many parts namely, measure groups, measures, calculations, actions, dimensions, dimension hierarchies, dimension attributes and dimension attribute relationships.
It is strongly recommended that drag and drop is used to create an OLAP Cube in order that all the components are set up correctly. OLAP Cubes can utilize a hierarchical structure in the dimensions to facilitate user queries. Therefore, each dimension present in an OLAP Cube must have either a hierarchy of levels or attributes and relationships. The hierarchies are defined against the underlying dimensional attributes which can be inherited from the source dimension metadata. Individual attributes can be added to the dimension after the OLAP Cube or OLAP Dimension metadata has been created.

Building a New OLAP Cube

To create an OLAP Cube proceed as follows:

  1. Double-click on the OLAP Cube object group to make the middle pane a cube drop target.
  2. Select the data warehouse connection to browse in the source pane. The connection can be selected by right-clicking the Data Warehouse connection in the Object list pane and choosing Browse Source System.
  3. Drag a Fact table from the source pane into the target pane.
  4. Set the cube name in the Add a New Metadata Object window and then click ADD.
  5. A prompt is displayed for any OLAP Dimensions that do not already exist that are required for this cube (based on the fact table metadata). Set the dimension name in the Add a New Metadata Object window and then click ADD.

    Note

    If you wish to include Attribute Relationships in Analysis Services for this dimension, click the Include Attribute relationships check box.
  6. The following window appears, prompting you to select the attributes to be included in the Customer OLAP dimension.

    The attributes available for selection are in the left column. To select an attribute, click the attribute in the left column and click > . This moves the attribute to the right column.
    To de-select an attribute, click the attribute in the right column and then click < . This moves the attribute to the left column.
    Repeat Step 5 for each dimension as required.
  7. A window appears with a list of the fields that are to be added as measures. Remove any columns that are not measures. A measure is a column that uses the sum, count, min or max of the column. Calculations can be chosen if required at this point. A date dimension must be present along with a hierarchy to enable the definition of these calculated members.
  8. During cube creation, the Adding Cube Measures window is displayed. In this window the following options are provided:
    • Measure, provides a list of measures that can be aggregated (e.g. using Sum, Count, Min, Max or Distinct Count). By default, WhereScape RED shows all attributes in the Fact table that are defined as numeric and additive. Attributes that should not be considered measures can be removed using the Remove button.
    • Calculated Member options enable you to add some predefined date based calculated member definitions to be built against the cube. The standard calculations:
      • Month to date
      • Year to date
      • Moving Quarter
      • Moving Year
      • Same Month Previous Year
      • Previous Year to date

    These defines a calculated measure based on the associated drop-down boxes. There are two different ways that WhereScape RED implements these calculations which is dictated by the Use OLAP functions check box:

    • Using OLAP Functions - implements the calculations using MDX Expressions within the cube using date based MDX functions. These calculations are efficiently executed by Analysis Services.
    • Without using OLAP functions - implements the calculations using an MDX Filter function built over date dimension attributes. This option leverages the flags from the relational date dimension and ensures that a query using the calculations in the Cube matches an equivalent query against the star schema and is particularly useful if non-standard date periods are used.
  9. The cube and dimensions is created in WhereScape RED metadata and the cube measures is displayed.

Setting Cube Properties

The properties of the cube must be completed before we can create the cube in the Analysis services database. Most of the elements in the properties screen has default values, but each of the following columns probably need to be completed.

  1. The Connection to the Analysis services server must be defined within the cube properties. This connection is a connection object. If no such connection exists then a new connection of type must be created and configured. SQL Server 2005 or 2008 Analysis Services use a connection type of "Analysis Server 2005+". This connection name must then be chosen in the cubes properties.
  2. A Cube Database Name must be selected. A new database name can be created by selecting (Define New Cube Database...) from the drop-down list. This database name is the database that the cubes will reside on the Analysis services server.
  3. The Data Source Connection must be defined and the three derived values shown under this connection must be present. If there is nothing in the three fields below the data source connection then the connection object will need to be modified. The provider type is normally MSDAORA. if the data warehouse is an Oracle database or SQLOLEDB if the data warehouse is SQL Server. The Server is either the SQL Server database server, or the Oracle TNS name. The database is the data warehouse database name.
  • No labels