Referenced Relationship Type in SSAS

By

-

January 12, 2012

Friends,

When the Dimension table and the Fact Table(also called as Measure Group Table) are related through some other dimension table then that type of relationship is called as Referenced Relationship Type. Lets take a small example from AdventureWorks database.

I am taking the following tables to demonstrate Referenced relationship type.

1) FactInternetSales – Fact/Measure group table

2) DimProduct and DimProductSubCategory – Dimension tables

In this case FactInternetSales and DimProduct tables have foreign key relationship on the column “ProductKey” and hence to fetch Product wise sales then the query looks like –

Select EnglishProductName,sum(sales_Amount) from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey
Group by EnglishProductName

Now if you wish to get data at Subcategory Level then the query looks like –

Select EnglishProductSubcategoryName,sum(sales_Amount) from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey
INNER JOIN DimProductSubcategory on DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
Group by EnglishProductSubcategoryName

If you observe the above query then it is clear that We are not using any column to display from DIMPRODUCT table but still we are using it to give reference between DimProductSubcategory table and FactInternetSales tables.

We used INNER JOIN to join FactInternetSales and DimProduct tables and the same relationship can be provided in SSAS cube which is called as Regular Relationship Type. As we cannot give regular relationship type between FactInternetSales and DimProductSubcategory tables as they dont have KEY to join(in other words not directly related), we can use REFERENCED Relationship type to relate those two tables. If we want to relate the above query with SSAS Cube implementation then DimProduct and DimProductSubcategory tables are Dimension Tables in Cube and FactInternetSales is Fact Table . PFB the screen with out providing relationship between dimension and fact.

Now lets see the steps to be followed to provide Regular Relationship Type between a dimension(DimProduct) and a Measure Group(FactInternetSales). Use the Define Relationship dialog box to define a relationship between a cube dimension and a measure group in Cube Designer. You can display the Define Relationshipdialog box by clicking … on a cell in the Grid pane on the Dimension Usage tab in Cube Designer.

1) Select the relationship Type as “Regular”.

2) Select the attribute that defines the granularity of the measure group with respect to the dimension. This attribute is usually the key attribute of the dimension.(ProductKey in our case).

3) Then it automatically displays Dimension Table and Fact Table.

4) It also displays the Dimension Table columns in RELATIONSHIP GRID and you have to select appropriate Measure Group Columns.

PFB the screenshot of the same.

5) After selecting Measure Group Columns click OK.

We set the relationship between InternetSales and Product using Regular relationship type. Now lets see how to give referenced relationship between FactInternetSales and DimProductSubCategory. Before going to that following things should be in mind while giving referenced relationship type.

1) The dimension which is acting as Intermediate should be created first.

2) The columns that are required to join the fact and second dimension should be selected as Attributes in the Intermediate dimension. From the above SQL query it is clear that my DimProduct dimension(which acts as bridge between fact and subcategory) requires a min of two columns as attributes and i.e ProductKey to join with Fact and ProductSubcategoryKey to join with DimProductSubcategory Dimension.

Now please find the steps below to give referenced relationship –

1) Select the relationship Type as “Referenced”.

2) Select the intermediate Dimension(In our case it is Dim Product).

3) Select the attributes from both the dimensions which forms a relationship between these two dimensions(In our case it is ProductSubCategoryKey).

4) Materialize option is checked and leave it as it is.

5) Click on OK.

That’s it .. It is as easy as this .. Happy Coding !!

Regards,

Roopesh Babu V

Drill Down in SSRS Reports

By

roopesh.valluru

-

January 6, 2012

73

Friends,

A drill down report is a type of report that displays summary data with links to related detail data in other reports. If a report has at least one group column the we can set Drilldown functionality to that report. You have to use VISIBILITY property to implement drilldown functionality in your report. Lets see how to configure Drilldown in a report. I dsigned a report with three group columns(category,subcategory and product) and few details. PFB the screenshot of the same.

In the above screenshot you can see the three groups created. Now PFB the steps involved in implementing drilldown feature for this report.

1) Right click on GROUP2 and select Group Properties.

2) Select Visibility option, select Hide from the options given for When the report is run initially:label, check the check box of Display can be toggled by this report item: label and select Group 1 from it’s drop down list as shown in below screenshot.

3) Repeat the same step for Group3 and select Group2 for this in the drop down list.

4) Now Preview the report and you can see Drill down report a s shown below.

That’s it and it is as simple as this. This is easy to configure but a very powerful feature in SSRS. Happy Coding !!

Regards,

Roopesh Babu V

Document Map Property in SSRS

By

roopesh.valluru

-

January 6, 2012

37

Friends,

Lets discuss about Document Map property in SSRS. The Document Map feature allows you to configure bookmarks for each instance in a group and display this list of bookmarks next to the report. In effect, this list of bookmarks is a table of contents for the report that enables the user not only to see the group instances at a glance, but also to jump to the location of a specific instance. Lets say I have a report which provides information on the products available in all categories and its sales. Lets see the steps involved in configuring document maps in reports with this simple example.

1) Design a report.

2) Add a Group to the column for which you want to configure document map and in this case I created group for Categories Field as shown below.

3) Right click on group and select group properties as shown below.

4) Select Advanced Tab in properties pane.

5) Select the Field from the drop down list and click on OK and in this case it is EnglishProductCategoryName as shown in the below image.

Then go to Preview report and you can find the Bookmarks on the left hand side and if you select any bookmark item then it will take you to the particular item in report. PFB the screenshot showing the bookmark for the report created.

That’s it .. Happy Coding !!

Regards,

Roopesh Babu V

RowNumber Function in SSRS

By

roopesh.valluru

-

January 6, 2012

1091

Friends,

Lets see what is RowNumber function in SSRS and a simple example to demonstrate the same. This RowNumber function returns the row number for every row with in the dataset. You have to pass the scope for which you wish to create row numbers. PFB the syntax for the same –

RowNumber(Scope)

Scope – This tell the server when to RESET the running count. If you pass DATASET name as SCOPE then it will not RESET until the all the items in the DATASET get over. Similarly if you secify a GROUP then the running count will not reset until all the rows items in the group is completed and it will repeat the same for all the groups. You can provide anyone of the below given objects as SCOPE of the function.

1) Dataset

2) Group

3) DataRegion

4) Nothing

If you provide NOTHING then it will take the OUTERMOST data region and will provide Running Count of the rows. Lets see an example on how to to get the Row Number of a SSRS report using ROWNUMBER Function. PFB the expression I am using to get the row number of each row of the data region.

=RowNumber(Nothing)

PFB the SC which displays the output of the expression given above.

You may be wondering “Why I need this ? ” and the answer is – There are many scenarios where you need this and lets discuss about one here. Lets say my requirement is to COLOR alternate rows in one color and in this case I can make use of ROWNUMBER Function to achieve the same. PFB the expression I have to use to achieve the given requirement.

=iif(RowNumber(Nothing) Mod 2,”Green”,”Yellow”).

PFB the steps you have to follow to achieve the same.

1) Select entire row.

2) Press F4(Properties Window).

3) Select BackColor Property and click on Expression.

4) Add the above given expression as shown below.

5) Preview report and you can see the report like given below.

You can use the same expression in many places like for changing font color,font weight,border color … So this is very useful function in designing your report.

That’s it, Happy Coding !!

Regards,

Roopesh Babu V

Fact Relationship Type in SSAS

By

roopesh.valluru

-

December 26, 2011

1301

Friends,

When the Dimension table and the Fact Table(also called as Measure Group Table) are SAME then that type of relationship is called as Fact Relationship. Lets take a small example from a very well known database to most of us i.e AdventureWorks.

Lets take a scenario like “A user wants to see the number of products available based on Color Type”. In this case “xColor” is my Attribute and that column is from DimProduct table and hence DimProduct is our Dimension table and the measure/metric is “Count” of products which you can get from DimProduct table again and hence our Measure Group table is ALSO DimProduct table. So,

Dimension Table – DimProduct and Attribute – Color

Fact Table – DimProduct and Measure – Count of Rows.

If you write query for the same then it looks like the query given below –

select Color,Count(*) as “Number of Products” from DimProduct Group By Color

The above query returns you the count of products by each color. Now lets see how it looks like in Cube Designer. Remember this is the only Relationship type that you dont need to configure. When you add the dimension to the cube and when you define measure group with the same table that is is used to create Dimension then SSAS Server automatically detects and configures. PFB the screenshot showing the Fact Relationship given between DimProduct Dimension and DimProduct Fact.

So, You dont need to worry of giving Fact relationship as the head ache of giving relationship will be taken by SSAS Server.

That’s it .. Happy Coding .. 🙂

Regards,

Roopesh Babu V

Copy Column DataFlow Item in SSIS

By

roopesh.valluru

-

December 26, 2011

49

Friends,

Copy Column Transformation helps you in creating new columns by copying input columns and adding the new columns to the transformation output. In simple it creates alias columns of the input columns. You can create any number of new columns by copying the same input column. For example using a column named “First Name” you can create any number of copied columns. Once the columns are created then you can apply transformations to the copied columns using other dataflow items. For example once the new column is created using copy column transformation then we c an convert the copied data to uppercase characters by using the Character Map transformation, or apply aggregations to the new column by using the Aggregate transformation.

PFB the steps involved in configuring Copy Column Transformation.

1) Drag and Drop a Dataflow item in Control Flow designer.

2) Right click and edit on data flow task or simply double click on the data flow item.

3) Select any Data flow source and configure it by providing source info.

4) Select Copy Column Transformation and edit it by right clicking on it.

5)Select the columns for which you wish to create a copy and give Alias names as shown in the below screenshot.

6) Click on OK and you can test by adding data viewers.

PFB the screenshot of the output of Copy Column Transformation. You can see that the transformation created a Copy of the column “Name”. This transformation has one input and one output and it doesn’t support an error output.

Note – When you have transformations like derived column, we don’t feel that this Copy column transformation is useful. Simply to say You can ignore this transformation .. 🙂

That’s it .. Happy Coding ..

Regards,

Roopesh Babu V

New Named Calculation in SSAS DSV

By

roopesh.valluru

-

December 22, 2011

172

Friends,

Named calculations are Virtual columns that can be added on tables in Data Source View(DSV). This named calculation acts as a Column in your table and this column won’t be reflected in your database. If you wish to extend the relational schema of a table or a view without modifying the underlying data source table or view then New Named Calculation is the correct option. One more and main advantage of “Named Calculation” is that these are calculated at the time of PROCESSING but not at QUERY Time and hence the performance will be good. The Named Calculation consists of the following things.

1) Name – Name of the Virtual Column

2) Description – Description about the Virtual Column(Optional).

3) Expression – Valid SQL Expression.

You can use one or more columns to define the expression for Named Calculation. For example to get full name as a new virtual column we have to use the below expression.

Title+’ ‘+FirstName+’ ‘+MiddleName+’ ‘+LastName

The above expression is using four columns to define Full Name Virtual column. PFB the steps to be followed to create the named calculation.

1. Right-click the table and select Create a named calculation.
2. Enter the calculation name as “Full Name” and enter a description.
3. Enter the following expression:

Title+’ ‘+FirstName+’ ‘+MiddleName+’ ‘+LastName

4. Once the OK button is clicked it will show if there are any errors in syntax. If there are no errors then you can see new Virtual column named “Full Name” with an icon “CALCULATOR” next to it as shown in below screenshot.

5. If you wish to test whether you are getting data as expected or not then you can you “EXPLORE DATA” option as shown in the below screenshot.

This is how you can use calculations. If you use the same expression which is given above to concatenate columns then you may not get desired result if we have NULLs in data. For example if Last Name is NULL for a column the <Some Value> + NULL is treated as NULL by SQL and hence we have to handle NULLs in expressions when we are working with String data. PFB the updated query which handle NULLs.

isnull(Title,”)+’ ‘+isnull(FirstName,”)+’ ‘+isnull(MiddleName,”)+’ ‘+isnull(LastName,”)

In the above expression I am checking whether the value is NULL or not and if it is NULL then I am replacing it with ‘BLANK Value.

Note – It is not mandatory that you have to use at least one column from table to create an Expression. The following are also Valid Expressions.

1) getdate()

2) 1 or any integer

3) ‘Dumy’ or any string

In short any valid SQL expression can be given as Named Calculation Expression.

That’s it and Happy Coding !

Regards,

Roopesh Babu V

Handling Division by ZERO errors in MDX Calculations

By

roopesh.valluru

-

December 22, 2011

381

Friends,

Handling Divided by Zero errors are very important in MDX. In other words we can call it as Divided by NULL errors in MDX. The error that show when you try to divide by Zero/Null is “1.#INF”. Lets see a small example.

If you see the below given query, it consists of a calculation which divided 1 with 0.

with member [Measures].[DivideByZeroError]
as 1/0
SELECT [Measures].[DivideByZeroError] on COLUMNS
from [Adventure Works DW]

The result of the above given query is 1.#INF as shown in the below pic.

Output of the calculation which divides 1 by 0

Now I have written a calculation to check (Sales Amount)/(Discount Amount) for all products and the query looks like –

with member [Measures].[Sales to Discount]
as [Measures].[Sales Amount]/[Measures].[Discount Amount]
SELECT {[Measures].[Sales Amount],
[Measures].[Discount Amount],
[Measures].[Sales to Discount]} on COLUMNS ,
[Dim Product].[English Product Name].[English Product Name] on ROWS
from [Adventure Works DW]

IF you run the above query you can see many values coming as “1.#INF” as Discount Amount is either NULL or 0 for many products and PFB Screenshot of the same.

Lets see how can we make use of IIF condition to avoid these errors. The IIF Condition syntax in MDX is as given below.

IIF(condition,true,false) – If the condition is TRUE then first part will be executed else second provided in the iff condition. The query looks like below after applying IIF condition.

with member [Measures].[Sales to Discount]
as iif([Measures].[Discount Amount] = 0,null,[Measures].[Sales Amount]/[Measures].[Discount Amount])
SELECT {[Measures].[Sales Amount],
[Measures].[Discount Amount],
[Measures].[Sales to Discount]} on COLUMNS ,
[Dim Product].[English Product Name].[English Product Name] on ROWS
from [Adventure Works DW]

In the above query I am specifying that if Discount Amount is 0 then place NULL for the product in the calculated measure output else calculate the value. The output will look like this –

That’s it and Happy Coding Folks !

Regards,

Roopesh Babu V

REFRESH Option in SSAS DSV

By

roopesh.valluru

-

December 22, 2011

573

Friends,

Lets have a small discussion about one of the most important options available in SSAS Data Source View i.e “Refresh”. I have never given or taken an interview with out discussing about this option. This is such an important option available in DSV. When ever there are changes occured in the schema of the underlying data source then yu can use REFRESH option in dsv to incorporate all the changes to the tables in the DSV. Refreshing a DSV updates the whole data source view.

You can find the option available in the RIGHT CLICK on DSV. PFB the screenshot showing the same.

Lets assume we have 10 tables added in DSV to design a cube and if there are any schema changes occurred in database level for these 10 tables. If you select on REFRESH option then it will go to the database and check for the changes occurred in these 10 tables and update all the changes to the tablels in DSV. The type of schema changes include –

Primary key defined or removed.
Foreign key relationship defined or removed.
Column Added or Removed.
Table Removed.

To demostrate the same I added one column “TEST” to DimCurrency table which is part of the DSV I designed and removed one column “DISCOUNTPCT” from the table DimPromotion and then I select Refresh in DSV and PFB the screenshot showing the list of changes occured in my underlying data source.

Be careful in refreshing the DSV. You can either update all the changes or reject all the changes. We cannot update Few changes ignoring the remaining. If you defined an attribute/measure using a column and that column is removed then it will remove that attribute/measure from the cube. You can you “Save Report” option available in the wizard to save the report about the changes that are updated on Refreshing DSV for your future reference.

Refreshing a data source view is based completely on the names of the underlying objects. Therefore, if an underlying object is renamed in the data source, Data Source View Designer treats the renamed object as two separate operations—a deletion and an addition. In this case, you may have to manually add the renamed object back to the data source view. You may also have to re-create relationships or logical primary keys.

That’s it and Happy coding !

Regards,

Roopesh Babu V

Perspectives in SSAS

By

roopesh.valluru

-

December 22, 2011

16

Friends,

Perspectives are the best way to reduce the complexity of the cube. The main advantage of perspectives is that it wont need any storage in Analysis Server beyond their definition. The following objects can be Hidden or Displayed using a perspective.

Attributes
Dimensions
Measures
Measure Groups
Hierarchies
Actions
KPIs
Calculated Measures

Perspectives are similar to Views in Relational database in that they narrow down the objects that can be seen from a single cube. Lets assume we have a table with 100 columns and using View you can show only limited number of columns that are required and in the same way using perspectives we can HIDE or SHOW the objects of SSAS Cube. For example, the Adventure Works cube in the Adventure Works DW sample Analysis Services database contains eleven measure groups and twenty-one different cube dimensions, representing sales, sales forecasting, and financial data. A client application can directly reference the complete cube, but this viewpoint may be overwhelming to a user trying to extract basic sales forecasting information. Instead, the same user can use the Sales Targets perspective to limit the view of the Adventure Works cube to only those objects relevant to sales forecasting. Lets see the steps to be followed to create a perspective.

Select the Perspectives tab in cube by double clicking cube first and then clicking Perspective tab next.
Click on New Perspective button which is rounded in the below given image.
In the first row of the Perspective Name column, select the default perspective name and enter a new name for the perspective. Please provide valid name which reflects the purpose of the perspective.
In the Perspective Name column of the new perspective, deselect Measure Groups and Dimensions for the perspective as desired. By default, all measure groups and dimensions are selected.
Process and Deploy the perspective.

To view the Perspective created go to Browser tab and select the Perspective as shown in below image.

To delete the perspective please follow the steps give below.

Go to Perspectives tab.
Select the perspective name which you wish to delete.
On the toolbar of the Perspectives tab, click the Delete Perspective icon to delete the perspective, or right-click in the Perspective Name column and select Delete Perspective.
Deploy and Process the cube.

PFB the screenshot of the same.

That’s it and hope this was helpful to you guys !

Regards,

Roopesh Babu V

Regular Relationship Type in SSAS

By

roopesh.valluru

-

December 22, 2011

42

Friends,

When the Dimension table and the Fact Table(also called as Measure Group Table) are directly related(i.e Foreign Key Relation) then that type of relationship is called as Regular Relationship Type. Lets take a small example from a very well known database to most of us i.e AdventureWorks.

I am taking the following tables to demonstrate Regular relationship type.

1) FactInternetSales

2) DimProduct

The above two tables have foreign key relationship on the column “ProductKey” and hence to fetch data from both the table the query looks like –

Select * from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey.

We used INNER JOIN to join the above two table and the same relationship can be provided in SSAS cube which is called as Regular Relationship Type. If we want to relate the above query with SSAS Cube implementation then DimProduct table is the Dimension Table in Cube and FactInternetSales is Fact Table and the Regular relationship Type is the JOIN which we provided in the above query. PFB the screen with our providing relationship between dimension and fact.

No Relationship given

Now lets see the steps to be followed to provide Regular Relationship Type between a dimension and a fact. Use the Define Relationship dialog box to define a relationship between a cube dimension and a measure group in Cube Designer. You can display the Define Relationshipdialog box by clicking … on a cell in the Grid pane on the Dimension Usage tab in Cube Designer.

1) Select the relationship Type as “Regular”.

2) Select the attribute that defines the granularity of the measure group with respect to the dimension. This attribute is usually the key attribute of the dimension.(ProductKey in our case).

3) Then it automatically displays Dimension Table and Fact Table.

4) It also displays the Dimension Table columns in RELATIONSHIP GRID and you have to select appropriate Measure Group Columns.

PFB the screenshot of the same.

5) After selecting Measure Group Columns click OK.

That’s it .. The regular relationship is set between a dimension and fact table.

Note – If you provide relationship between dimension table and fact table in SSAS DSV(Data Source View) and the relation ship is direct then Regular will be automatically set by SSAS Server when we add Dimension and Fact. So the best and easiest way is to give relationship in DSV level.

Regards,

Roopesh Babu V