Adding/Creating a Hypercube

WC BI uses a standardized set of tables from the ERP’s we support known as the DataLake, which is used to publish data to the cubes during the nightly process.

New options on the Admin Menu, which require Admin level access to WC BI, allow users to add or modify the contents of the DataLake and the Cubes

• • Data Pump Configuration – Used to add/edit tables to the Datalake
  • ETL Configuration – Used to add/edit Tasks and Cubes 

Creating a new cube in WC BI

This requires the user to prepare a list of tables that contain the facts, information that is being measured, and dimensions, information that describes the facts for sorting and aggregating. 
 

The first step in creating a new Cube is determining if the required tables are in the DataLake 

• Select Data Pump Configuration from the Admin menu 

If your tables are part of the ERP database, you can use the standard Connection to use them. If they come from a different data source, you can add a new Connection 

• Select Connections if you need to create a new Connection to a data source 

• Select Upload Config to import a connection file   
• Select New Connection to create a Connection manually 

• Input a name for your new Connection (ODBC?, Multivalue?) 
• Select the Source Type using the radio button 

JDBC – a connection via JDBC to a database, SQL Server or Progress, or another database type using a driver installed on the Data Pump server that populates your DataLake

FILESYSTEM – a .CSV file containing columns separated by commas with a header row to provide column names and detail rows with your data 

CSD – a connection to an INFOR CSD datalake via an IOAPI file and a database driver

MULTIVALUE – a connection to a multi-value file system using source type, UNIVERSE or UNIDATA, and server information 

• • Select Save to save your new connection If you made an error, you can Delete your new connection but do not delete any other connection information that currently exists 

The next step is to determine if your tables are already in the DataLake, if you used an existing Connection, or if you need to add them for a new Connection 

• • Select Tables from the menu bar 
  • Type the table name into the Search box or scroll through the list of tables 
  • If you don’t see the tables you need then you will need to add them  

• • • Select New Table  
    • Input Table Name  
    • Select Connection  
    • Use Infer SQL to select all records from the table, you can edit the SQL if you want to apply any filtering or formatting 
    • Select Save
    • Repeat as needed for additional tables 

Scheduling Newly Created Tables

The next step is to schedule any new tables that you have added 

• • Select New Schedule to add a Schedule 

• • • Input a name for the Schedule 
    • Select the days of the week, hours and minutes of the days for which you want to run the Schedule 

• • • Input the names of the tables you added into the DataLake tables input box 
    • Input the names of the tables you added into the DataLake tables to clear input box 
    • Select Save to save your schedule 
    • You can delete a Schedule using the red x icon or edit it using the blue pencil icon or run the Schedule using the green arrow icon 
    • Do not delete any schedules that you did not create

You can check on the progress of your new schedule using the Status menu option and the green Refresh button 

• • The Finished Type column will tell you if your Schedule was successful 
  • The Error Message column will tell you if your Schedule has any errors 
  • It can take time for the Schedule to run if you started it manually so you may need to use the Refresh button several times before the results are displayed 
     

• After you have added any required tables to your DataLake the next step is to add any new Tasks to extract data from the tables you have added or edit existing Tasks for the current set of Cubes 

• • Fact tasks are used to populate the contents of a cube with Measures, the values that are being aggregated over time, Dimensions, the descriptions of the aggregated data used for filtering and sorting, and Details, which describe the individual records contained in the cube with information that is not aggregated or pre-sorted 
     

•  Select ETL Configuration from the Admin Menu 

• • Select the Task you want to edit from the list of Company Tasks, Tasks which have already been edited, or Default Tasks 
  • Select Override Depends On to add your new table names to the list of tables that must be populated for the task then input the names of the tables into the list 
  • If you want to add a column to the existing Fact table or Dim tables then you can use the Add External Column option 

• • • Select Add External Column to link a field from a table that you added to the DataLake to the Task 
    • Input the field name used to join the tables in the Join Column input box 
    • Select Add Column map to indicate which columns you want to extract from the table you added and what to call the column in the Task 
    • Input the Source Column name from the new table into the Source Column input box 
    • Input the Destination Column name into the Destination Column input box, this allows you to rename the destination column to a more user-friendly name if the source column name is unclear 

• If you want to edit the SQL directly you can select the Override SQL slider to enable editing of the SQL for the Task 

• • • Add comments to the SQL to indicate who is changing the SQL, when it occurred and why 
    • Change the SQL as needed, it is recommended that you copy the existing SQL from the task into a text document to preserve the original contents and that you test any changes you are making to the SQL using a SQL editor tool if you have one that is already connected to your source database 

• After you have made your changes input an explanation into the Edit Comment input box and then select Save Task 
   
• Select New Task to add a new Task to the list of Company Tasks if you are creating a new Fact Task to populate a cube or a new Dim Task if you are adding a new dimension to a cube that requires descriptions of the values in the column used to populate the record id values in the Cube 

• • Use the steps previously described to add the required content to your new Task 
     

• Once you have added or edited all of the Tasks required to make the changes to an existing Cube or to add a new Cube you can select the Cubes menu option to apply those changes 

• Select the Cube you want to edit from the list of cubes or select New Cube to create a new Cube 
• If you are creating a new Cube, then input the Cube name into the Cube Name input box 
• If you are creating a new Cube, then input a description of the Cube into the Cube Description input box 

• If you are creating a new Cube, then input the Fact Table name from the Fact task you created into the Fact Table input box 
• If you are creating a new Cube, then input the field name from the Fact task you created into the Date Dimension input box that contains the transaction date from the fact records in your new fact table 
• Input the name of any columns that you want to use as Measures in the cube by inputting the names into the Measures input box 
• Open the Detail section and then select Add Detail Columns to add a new field to the list of details that you can select as a column in a report from your cube 

• • • Input the column name from your fact table into the Column Name input box 

• • • Input a description for the column into the Column Heading input box to use when the detail field is displayed in the list of Detail fields for the Cube 

• Open the Dimensions section and then select Add Dimensions to add a new Dimension to the cube for the columns in your fact table task that you want to use to aggregate, sort and filter the values from the Measures you have added to the Cube 

• • • Input the column name from your fact table into the Fact Table Column Name input box 
    • Input a description for the column into the Title input box to use when the Dimension is displayed in the list of Drilldown options for the Cube 
    • Input the name of the DIM table task you created to contain the descriptions of the values in the field you added to the fact table as record ids into the Table input box 

• Input a value that you want to use as the abbreviation for the Dimension/Drilldown Option into the Abbreviation input box, this abbreviation will be used in the MQL that creates reports from your cube so it should be short and unambiguous, like TERR for Territory as an example 

• • • Repeat the steps for adding Measures, Details and Dimensions as needed until you have completely described your new Cube or finished adding content to an existing Cube 
    • Input an explanation of the changes you made into the Edit Comment input box and then select Save Cube to save your work 
    • You cannot delete a cube once you have added it, but you can edit the contents as needed 

Try to avoid creating cubes that have similar content to existing cubes or other cubes you have already created unless you absolutely need to do so, it is better to add content to an existing cube if possible or to make any new cubes that you create broad enough in scope that they can support many different reports that users want to create in WC BI