Enabling Drillthrough for a cube

The drillthrough feature allows the user to see the individual rows and values from the fact table that were input into a cube.

a)      On Cube Editor Tools menu click Drillthrough Options

b)      In the Cube Drillthrough Options dialog box, select Enable Drillthrough checkbox, click the Select All Button, and Click Ok.  Click Ok again when warned that the cube must be saved.

c)  Now click the Process cube button, accept the offer to save the cube, decline storage creation and click Full Process processing option, click Ok, and then close the process log window.

d)      Browse the data in the Data Tab 

Drillthrough is a useful feature, but when used in a cube its usefulness is limited to the amount of data that is already stored in the cube’s fact table.

The Order By Property of the dimension and its impact on cube data

Certain applications like the finance applications aggregate in complex ways.  It is very important that the data is presented in some kind of order.  The Order By property of the cube is used to set the order in which data is to appear in the output browser of the cube.  To set the Order By property of a dimension:

a)      In the Dimension folder select the dimension and click the Advanced tab in the properties pane to change the All Level Property to No.

b). Though the Dimension key has no impact on the Analysis server, the client application may derive some benefit by sorting out the order of account members.  Expand the dimension and select the level and change its Order By Property to Key and press enter. 

c)      The dimension members now appear in the proper order.

Using Custom Rollup Operators and Custom Member formulas with cubes

Custom rollup operators are used to properly aggregate values along a dimension.  Each member of the dimension needs its own aggregation rule. It may be recalled that aggregation rules consist of single-character codes and these codes are simple arithmetic operators called unary operators. Custom Member formulas provide values for specific members in a fact table and the rules are stored in Multidimensionsal Expressions(MDX).  This was discussed in detail in the earlier tutorial “Using Advanced Dimension Settings”. 

Creating a cube from a measureless fact table

Some fact tables have no quantitative measures.  Yet data from these tables need to be analyzed in different ways and results obtained.  For instance a visitor dimension may contain only rows and columns which tell you the id’s of visitors who visited a store in a particular region or district.  There may be no quantitative value attached to the information.  You may need to have a count of the number of distinct visitors who visited the store.  This is done by creating a count measure.  The visitor id is dragged and dropped into the measures folder and the Aggregation parameter is set to count and the cube is processed.  The Data tab then returns the visitor id and the number of visits made by that visitor.  If the aggregation parameter is set to Distinct Count the data returned would tell the user the number of distinct visitors the store received.

Very large dimensions with a large number of rows of data may require some amount of grouping to derive some sense from the information.  Setting the grouping property to automatic will create groups from the data.  The groups are added to the dimension only when the cube is processed.  In the Data tab, the group levels are displayed with the + sign and can be collapsed and expanded to view the data.  It must be noted that the user has no direct control over the number of groups created automatically by the Analysis services.

Creating a Cube from an Empty fact table

Normally the values in a sales forecast cube will be entered by the user and a fact table may not be required.   However, a cube cannot be created without a fact table and hence sometimes an empty fact table is used to create a cube.  The fact table will contain columns but no data or rows.  It is a placeholder.  The user will get a message that there are no rows in the fact table when the dimension is selected.  A non-zero value will have to be manually entered as the number of fact table rows before processing the cube.  To do this, the user needs to select the cube and on the Advanced Tab of the properties pane, type 1 as the value of the Fact Table Size Property and then click the Process Cube button.

Write enabled cubes and Writeback data

Cube data can be configured so that end users can make changes to it.

Next Page: Tutorial 41: MSAS: Defining Cube Properties - Page 3