MSBI # 18– SSAS # 4 – Building a Cube and Dimension Using SSMS–SSAS Wizards

Namaste friends !!

Continuing from my last post MSBI # 17– SSAS # 3 Lets Get Start with SSAS ,Features and Project Start up– (SQL Server Analysis Services) « (B)usiness (I)ntelligence Mentalist

Some of my reader always suggested me to always write down less but give the demo and steps to perform whenever I write post about any BI topic

And I also complain me that I have always write down less about SSAS

So listening to each and every aspect of them I have started thinking of providing most of thing to my sweet reader at my level best !!

So start and taking Second step towards SSAS understanding lets learn how to build cube using cube wizards

Building a Cube Using the Cube Wizard

We create Analysis Services projects in Business Intelligence Development Studio by using the Analysis Services Projects template. The Analysis Services project helps you organize and manage the items that are required to build, debug and deploy an Analysis Services database.

The solution you are to develop is an Analysis Services cube, which is one of the most common tasks in Analysis Services. To help you with this, you have the new Cube Wizard with IntelliCube technology. This wizard simplifies creating an Analysis Services cube significantly. A cube and its dimensions can be created by using a single wizard because IntelliCube technology can detect the relationships between attributes, dimensions, and facts.

To create an Analysis Services cube, you will:

  • Create a Data Source containing the information Analysis Services needs to connect to a database.
  • Create a Data Source View containing schema information and Named Calculations.
  • Create a Cube containing data from multiple fact tables that have different dimensionality and granularity.
  • Process and browse the Cube.

image

For this demonstration I am going to use Adventure Work database which is all time available and Hit Database .

This table illustrates using the Cube Wizard to quickly develop a cube.

Understanding Note

Steps to Perform

TUsing Business Intelligence Development Studio, open the SSAS solution in the C:\BI\Part 2\Module 05\Demos folder.

a. Click Start, All Programs, Microsoft SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.

b. On the File menu, point to Open, and click Project/Solution.

c. Navigate to C:\BI\Part 2\Module 05\Demos, and double-click SSAS.sln.

The data source connects to the AdventureWorksDW database on the SQL Server host computer.

d. In Solution Explorer, double-click Adventure Works DW.ds.

e. Click Edit to review the connection manager details.

f. Click Cancel twice to close all dialog boxes.

Notice the tables defined in the data source view. In this scenario, only one fact table is defined and selected dimension tables are included. Friendly names have been assigned to each table. Also, notice the named calculations in the Time table.

g. In Solution Explorer, double-click Adventure Works DW.dsv.

h. In the Time table, scroll if necessary to view the named calculations, Quarter and Month.

Launch the cube wizard.

i. In Solution Explorer, right-click the Cubes folder, and click New Cube.

j. On the Welcome page, click Next.

Notice the options for creating a cube. In this scenario, you use the option to create a cube from existing tables.

k. On the Select Creation Method page, click the non-default options to read the corresponding descriptions, and then click Use existing tables.

l. Next.

The cube wizard has been changed to first prompt you to select only measure group tables.

m. On the Select Measure Group Tables page, select the ResellerSales and InternetSales check boxes, and then click Next.

Next you select the measures to include in the cube.

n. On the Select Measures page, select the Measure check box to clear all selections.

o. Select the check boxes for the following measures: Order Quantity, Total Product Cost, Sales Amount, Order Quantity – Internet Sales, Total Product Cost – Internet Sales, and Sales Amount – Internet Sales.

You can rename measures here also. Remember that measure names must be unique within the cube.

p. Right-click Order Quantity, and replace the text with Reseller Order Quantity.

q. Repeat the previous step to rename measures as shown in the following table:

Measure Revised Name

Total Product Cost Reseller Cost

Sales Amount Reseller Sales

Order Quantity – Internet Sales Internet Order Quantity

Total Product Cost – Internet Sales Internet Cost

Sales Amount – Internet Sales Internet Sales

r. Click Next.

Next select the dimensions to include. In this scenario, because you will create the Product dimension using the dimension wizard later, clear the selection. In addition, Reseller Sales and Internet Sales are used exclusively as fact tables in this scenario, so you must clear these selections.

s. On the Select New Dimensions page, clear the Product, Reseller Sales, and Internet Sales check boxes.

t. Click Next.

Name the cube Sales.

u. On the Completing the Wizard page, in the Cube name box, type Sales.

Review the results on the Cube Structure and Dimension Structure pages. Notice the new dimensions are created with only the key attribute.

v. After reviewing the Cube Structure page, in Solution Explorer, double-click Reseller.dim.

w. Double-click Time.dim.

x. Double-click Sales Territory.dim.

Building a Dimension Using the Dimension Wizard

 

image

In this demonstration, you use the Dimension Wizard to develop the product dimension.

Understanding Note

Steps to Perform

Launch the dimension wizard.

a. In Solution Explorer, right-click the Dimensions folder, and click New Dimension.

b. On the Welcome page, click Next.

Notice the options for creating a dimension. In this scenario, you use the option to create a dimension from existing tables.

c. On the Select Creation Method page, click the non-default options to read the corresponding descriptions, and then click Use an existing table.

d. Next.

In this scenario, create the dimension based on the Product table. Notice the key column is selected, but you have the option to add more columns to address situations where a composite key is required to make each dimension member unique.

e. On the Specify Source Information page, in the Main table drop-down list, select Product.

f. In the Name column drop-down list, select EnglishProductName, and click Next.

Keep the default selections for related tables.

g. On the Select Related Tables page, click Next.

Select attributes for the dimension.

h. On the Select Dimension Attributes page, select the following attributes (in addition to the key attributes already selected from each table associated with the dimension):

Color

List Price

Size

Disable browsing for List Price. This attribute will be used for reporting, but not for analysis. That is, the user will not be able to put this attribute on rows or columns.

i. In the List Price row, clear the Enable Browsing check box.

j. Click Next.

Keep the default dimension name, Product. On the Dimension Structure page, notice the attribute for List Price is gray to indicate it is unavailable for browsing.

k. On the Completing the Wizard page, click Finish.

Add the dimension to the Sales cube.

l. In Solution Explorer, double-click Sales.cube.

m. Right-click in the Dimensions pane, and click Add Cube Dimension.

n. In the Add Cube Dimension dialog box, select Product, and click OK.

Hope this helps !!

If you Like my post please don’t forget to subscribe my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 14 : August 8 – August 10

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: