Appendix: Using Essbase Cube Builder Demo Data

This chapter provides an overview of Essbase Cube Builder demo data and discusses how to:

• Apply project CUBE_DEMO_PROJ.

• Grant permissions to all menu CUBE_TEST components.

• Import PS_CUBEBUILDER.DAT file using Data Mover script PS_CUBEBUILDER.DMS.

• Grant query security.

• Add data to tables.

• Use sample dimensions and outlines.

Understanding Essbase Cube Builder Demo Data

Essbase Cube Builder demo data is being provided as part of PeopleTools 8.50 so that you can get an idea of the structures involved in creating cubes.

Project CUBE_DEMO_PROJ is delivered in the PROJECTS folder of the installation. Data mover and data files, ps_cubebuilder.cat and ps_cubebuilder.dms, are also being delivered. These files load the necessary data to show prebuilt dimensions and outlines as well as demo data that you can add to, as needed.

Applying Project CUBE_DEMO_PROJ

Project CUBE_DEMO_PROJ contains tables for a cube test suite. This project also includes a new menu—CUBE_TEST—and portal registry entries for a new folder and components. All demo-date portal-registry entries appear under the Essbase Cube Builder folder.

Project CUBE_DEMO_PROJ also includes queries CUBT_ACCOUNTS, CUBT_TRANSACTIONS, CUBT_TRANSDATES, and CUBT_TRANS_ALL.

After importing project CUBE_DEMO_PROJ, create all tables and then build the view PSCUBTTRANSD_VW.

Project CUBE_DEMO_PROJ is delivered in the PROJECTS folder as part of the PeopleTools 8.50.

Granting Permissions to All Menu CUBE_TEST Components

Granting permissions to all menu components is often completed to permission list PTPT1200.

To grant permissions:

1. Select PeopleTools, Security, Permissions & Roles, Permission Lists.

2. Open permission list PTPT1200.

3. In the Pages page, add new menu CUBE_TEST.

4. Click the Edit Components link of menu CUBE_TEST, and grant permission to every component.

   The Component Permissions page for Cube Manager appears.

After permission is granted, a new folder called Cube Tester Sample Data appears under the Essbase Cube Builder folder. Items in the Cube Tester Sample Data folder are used for Accounts, Products, Regions, Sellers, and Transactions. You can add new items to any of these components to create more demo data.

Importing PS_CUBEBUILDER.DAT File Using Data Mover Script PS_CUBEBUILDER.DMS

The data mover script PS_CUBEBUILDER.DMS includes the following items:

• Tree structures.

  Includes two tree structures: CUBT_REGIONSTRUCT and CUBT_PRODUCTS.

• Trees.

  Includes two trees: CUBT_PRODUCTS and CUBT_REGIONS.

  The tree CUBT_PRODUCTS is linked to the product table and makes use of setID controls to show how these controls work in trees.

  The tree CUBT_REGIONS is linked to the region table.

• Data for all the tables that are imported in project CUB_TESTDATA.

The data import file PS_CUBEBUILDER.DAT includes all this set control data that is used for the trees and the sample data:

• SetID

  Includes three setIDs: EUR, SHARE, and USA.

• Record groups

  Includes one record group: CUBETEST.

• Tableset controls

  Includes some tableset rows for set IDs: EUR, MODEL, SHARE, and USA.

Both PS_CUBEBUILDER.DAT and PS_CUBEBUILDER.DMS files are delivered as part of PeopleTools 8.50.

Note. The data mover file (PS_CUBEBUILDER.DMS) contains the IGNORE_DUPS option, which imports the data and will not overlay any possible user data that has the same keys. When data mover contains this IGNORE_DUPS option, it works only if you log into Data Mover in bootstrap mode.

Granting Query Security

To grant query security:

1. Select PeopleTools, Security, Query Security, Query Access Manager.

2. Open the tree QUERY_TREE_PT.

3. Add the following records under the OLAP - Cube Manager Record node:

   • PSCUBTTRANS

   • PSCUBTSELLER

   • PSCUBTREGION

   • PSCUBTPRODUCT

   • PSCUBTACCOUNT

   • PSCUBTTRANSD_VW

4. View the queries that were imported from the project.

   Note that:

   • Query CUBT_ACCOUNTS can be used to build a dimension structure.

   • Queries CUBT_TRANSACTIONS and CUBT_ALL_TRANSACTIONS can be used to populate the cube structure.

   • Query CUBT_TRANSDATES can be used to create a time dimension, and it displays all unique transaction dates from the transaction table.

Adding Data to Tables

You can add data to the tables after you apply project CUBE_DEMO_PROJ, grant permissions to all menu CUBE_TEST components, import .DAT file, and grant query security.

The PSCUBTTRANS table contains all the transactional data that is used to populate the cube. The other tables contain definitional data with:

• Sellers—PSCUBTSELLER

• Regions—PSCUBTREGION

• Products—PSCUBTPRODUCT

• Accounts—PSCUBTACCOUNT

All tables are accessible from the new components.

Using Sample Dimensions and Outlines

These sample dimensions are also included in the project CUBE_DEMO_PROJ:

• CUBT_ACCOUNTS

• CUBT_ALL_REGIONS

• CUBT_EUROPEAN_PRODUCTS

• CUBT_TRANSACTION_DATES

The sample outline CUBT_OUTLINE makes use of the preceding dimensions. It also uses query CUBT_ALL_TRANSACTIONS as the query to populate the outline.