CubeBuilder, which is accessible through the MarketWide tab in SQLManager, is used to create rapidly accessible cubes. Resembling a mini database, cubes contain cube tables which are user built queries, unions, or joins created in their corresponding modules. These cubes of data can be stored on the MarketWide host, or can be empty shells, in which case data will only be present when a query is run against the cube. Additionally, cubes can be set to refresh either on demand or on a schedule.
The CubeBuilder Toolbar
The CubeBuilder toolbar provides quick access to commonly used commands in CubeBuilder. Click the icon once to carry out the action represented by that icon.
Opens the Properties dialog.
Edits the composition of the currently highlighted cube table.
Creates a duplicate of the currently highlighted cube table.
Removes the currently highlighted cube.
|Checks to optimize all cube steps.|
Saves the current cube and publishes the contents to the MarketWide tab in SQLManager.
Actions are the object type of the cube table. The action informs you whether the table is pulling from a query, a join, a union, or if you are creating an additional group to add more steps to the cube. Each step is the order in which the cube tables will be executed.
Creates a cube table based upon a query.
Creates a cube table based upon a join.
Creates a cube table based upon a union.
Creates a cube table based upon a rollup table.
Creates an additional step in the cube building process.
CubeBuilder Main Form
The CubeBuilder main form lists current cube tables and provides access to other modules that are used to build new cube tables. Cube tables can be a query, a join, a union, or a rollup. Additionally, the cube tables can pull data from either the database or the cube itself.
How to Create a Cube
- Drag and drop an action into the data collection area.
- Build the cube table.
- Drag and drop additional group nodes as needed.
- Repeat steps 1-3 as needed.
- When you have added all of the cube tables, click the publish button ( ).
- Select the refresh schedule and click OK.
- Enter in the desired cube name and click OK.
- You will receive a notification that "MarketWide Cube is currently being built".
- For a new cube, you must first build a cube table off the database before you can build from the cube.
- When an error occurs in a cube table, for instance, a field name is mistyped in a query, then all cube tables preceding the one containing the error are disabled until the error is fixed.
- If a cube table is checked as visible and the output type is set to View, then any cube table that it is dependent on it will be locked in as visible.
Step Properties will give you additional settings to customize how each step functions. You will see the step properties display at the bottom-left corner when an action has been selected.
- Name - The cube table name of the selected step.
- Comment - Where notes about the cube table can be made. This is useful for giving a brief description of the cube table.
- Source - The object(s) the data is being pulled from. This can be either database objects or cube tables.
- Output Type - Indicates whether the cube table is set as a table, a view, or MarketWide automatically determines which output type to use. Views perform more efficiently than tables when a cube table is pulling large amounts of data.
- Is Visible- Indicates whether the table is visible. MarketWide allows for cube tables to either be visible or hidden on the MarketWide tab in SQL Manager. Checking the box next to "Visible" allows the cube table to be seen by all users on the MarketWide tab. De-selecting the check box makes the cube table invisible, unless the cube is being edited.
Cube properties holds miscellaneous settings of the cube such as the description of the cube, ability to create/edit tags and the refresh interval for the cube.
User description for the cube that will appear at the bottom of the CubeBuilder screen.
Assign tags to the currently selected cube.
The hyperlink on the bottom of the dialog indicates how often the cube will be refreshed. By clicking on it, you can access the Refresh schedule dialog which will indicate the current schedule and allow you to edit it.
You can access Cube Properties in 3 different ways
- By right-clicking on the name of a published Cube and selecting Properties.
- Selecting the Properties icon ( ) in the CubeBuilder toolbar.
- Viewing Cube Properties in the bottom-left corner of CubeBuilder.
CubeBuilder allows you to specify how often your cube will be refreshed. You can either specify a schedule or have the cube refresh on demand.
- Refresh on demand - Whenever the cube is used, it will be refreshed. Additionally, the cube can be manually refreshed.
- Specify Lifetime - The amount of time the data can be used before the cube will need to be refreshed.
- Refresh interval - This sets the interval with which the cube will be refreshed. This interval can be measured in weeks, days, hours, etc.