Browse Cube Data – Change the Dimensions in the Grid

In this section, you will learn How to replace a dimension in the grid

To replace one dimension in the grid with another, drag the dimension from the top box and drop it directly on top of the column you want to exchange it with. Make sure the pointer appears with a double-ended arrow during this process.

Using this drag and drop technique, select the Store dimension button and drag it to the grid, dropping it directly on top of Measures. The Store and Measures dimensions will switch positions in Cube Browser.

OLAP Cube

How to filter your data by time

  • Click the arrow next to the Time dimension.
  • Expand All Time and 1998, and then click Quarter 3. The data in the grid is filtered to reflect figures for only that one quarter.

Cube Browser

How to drill down

  • Switch the Product and Customer dimensions using the drag and drop technique. Click Product and drag it on top of Country.
  • Double-click the cell in your grid that contains Breakfast Goods. The cube expands to include the subcategory column.

Cube Browser

  • You can expand any ‘+’ sign and see more detail. Or you can click any ‘-‘ sign and see the aggregate (less detail).

Browse the Cube Data in Microsoft Analysis Services

In this section, you will learn how to browse and slice and dice the data in your cube.

Using Cube Browser, you can see data in different ways, you can filter the amount of dimension data that is visible, you can drill down to see greater detail, and you can drill up to see less detail.

In the Analysis Manager tree pane, right-click the MySalesCube cube, and then click Browse Data.

Cube Browser appears, displaying a grid made up of one dimension and the measures of your cube. The additional four dimensions appear at the top of the browser.

Cube Browser

In the next section you will learn how to replace the dimensions in the above grid

Design Storage and Process the Cube in Microsoft Analysis Services

In this section, you will learn how to design storage for the data and aggregations in your cube.

Before you can use or browse the data in your cubes, you must process them.

You can choose from three storage modes: multidimensional OLAP (MOLAP), relational OLAP (ROLAP), and hybrid OLAP (HOLAP).

Analysis Services lets you to set up aggregations. Aggregations are pre-calculated summaries of data that greatly improve the efficiency and response time of queries. When you process a cube, the aggregations designed for the cube are calculated and the cube is loaded with the calculated aggregations and data.

In the Analysis Manager left pane, expand the Cubes folder, right-click the MySalesCube cube, and then click Design Storage.

In the Welcome step, click Next.

Select MOLAP as your data storage type, and then click Next.

Microsoft Analysis Services  Storage Design Wizard

  • Under Set Aggregation Options, click Performance gain reaches. In the box, enter 60 to indicate the percentage.
    You are instructing Analysis Services to give a performance boost of up to 60 percent, regardless of how much disk space this requires. Administrators can use this tuning ability to balance the need for query performance against the disk space required to store aggregation data.

    Microsoft Analysis Services Storage Wizard

  • Click Start. You can watch the Performance vs. Size graph in the right side of the wizard while Analysis Services designs the aggregations. Here you can see how increasing performance gain requires additional disk space utilization.
  • When the process of designing aggregations is complete, you can see how much storage is needed to achieve 60% gain. If you think the space is to much, you can hit Reset button and choose a lower number for the gain. On the other hand if you think you have enough space, you can hit Reset and increase the gain and click on continue to re calculate.
  • click Next and the Analysis Services will start processing the cube. In this process the aggregations are computed and stored in the cube.

Microsoft Analysis Services  Process

  • Finally close the Dialog Box and return to Main Window of the Analysis Services.
  • In the next section you will learn how to browse the data in MySalesCube that you just finished creating.

Cube Editor – Microsoft Analysis Services

In this section we will learn how to make changes to the Cube that we just designed using the Cube editor.

Cube Editor - Microsoft Analysis Services

  • Click on Insert menu and choose Tables
  • In the Select Table screen, choose Promotion table and Add the click Close
  • On the cube editor, double click on promotion_name and in the next Map The Column dialog box, select Dimension and click OK
  • You will see a new node called Promotion Name in the left panel under Dimensions.
  • Rename this node to Promotions
  • Close the Cube editor and save changes. Choose No when prompted to design the storage.
  • You will see how to design the storage in the next section

Create Store Dimension in Microsoft Analysis Services

In this section we will create the Store dimension

Click Create Dimension button again

Unlike our product dimension, all the Store data is present in a single table called Store. So we will choose Star Schema and click Next

You will see ‘Select the dimension table’ screen. select  the Store table

Choose ‘Standard Dimension’ in the next ‘Select Dimension Type’ screen

In the Select The Levels screen choose Store Country, Store State, Store City and Store Name fields in that order

Select the levels of dimension

 

 

  • Click Next and and you will see ‘member key columns’ screen. Simply click next here.
  • In the next ‘Advanced Options’ screen click Next
  • In the next screen, choose a name ‘Store‘ for our newly created Dimension. Make sure we share this product dimension across other cubes by checking the ‘Share the dimension’ box.
  • You will Finish building the Cube in the next section

Create Customer Dimension in Analysis Services

In this section we will create the Customer dimension

Click Create Dimension button again

Unlike our product dimension, all the customer data is present in a single table called customer. So we will choose Star Schema and click Next

You will see ‘Select the dimension table’ screen. select  the Customer table

Choose ‘Standard Dimension’ in the next ‘Select Dimension Type’ screen

In the Select The Levels screen choose Country, State_Province and Lname fields in that order

Dimension wizard

  • Click Next and and you will see ‘member key columns’ screen. Simply click next here.
  • In the next ‘Advanced Options’ screen click Next
  • In the next screen, choose a name ‘Customer‘ for our newly created Dimension. Make sure we share this product dimension across other cubes by checking the ‘Share the dimension’ box.
  • You will create the Store  Dimension Table in the next section

Create Product Dimension in Microsoft Analysis Services

In this section we will create the Product dimension

Click Create Dimension button again

Unlike our Time Table, the product table is spanning across two tables so we will choose Snow Flake Schema and click Next

Choose ‘Standard Dimension’ in the next ‘Select Dimension Type’ screen

Next You will see ‘Select the dimension table’ screen. Double click and select  product and product_class tables

Select the dimension tables

 

  • Click Next and make sure the two tables are joined by the product_calss_id field.

Create and Edit Joins

 

  • Click next and choose the levels by double clicking the column names in left panel. Our Product levels are Category, Sub category and Brand in that order.

 

Select Levels in Dimension

 

  • Click Next and and you will see ‘member key columns’ screen. Simply click next here.
  • In the next ‘Advanced Options’ screen click Next
  • In the next screen, choose a name ‘Product’ for our newly created Dimension. Make sure we share this product dimension across other cubes by checking the ‘Share the dimension’ box.
  • You will create the Customer  Dimension Table in the next section

Create Time Dimension in Microsoft Analysis Services – Continued

Choose Time Dimension in the following Select Dimension Type screen also in the Date Column, select the_date as your Date Column.

Dimension Wizard

  • Click next and you will see the ‘Create Time Dimension levels’ screen

Dimension Wizard

  • Notice that the wizard processes the Time table and pre-determines that your table contains Year, Quarter, Month, Day, Week, Day, Hour and Minute in that order. Choose ‘Year, Quarter, Month, Day’ as your time levels and hit next.
  • In the following screen make sure ‘Changing Dimension’ is unchecked and click Next
  • In the final ‘Finish the Dimension Wizard’ screen type the  Time for Dimension Name.
  • Make sure ‘Share this Dimension with other Cubes’ is checked. This will enable your newly created Time dimension shareable across your cubes.

Dimension Wizard

  • You will create the Product  Dimension Table in the next section
Copyright 2005-2016 KnowledgeHills. Privacy Policy. Contact .