Chennai SQL Server User Group - Feb 2013 Session - Power Pivot & Tabular Model

I had an oppurtunity to present in Chennai SQL Server User group and it was an awesome experience.The Session contents of Feb 2013 session can be downloaded from the below link

http://sdrv.ms/16Haogt

As the session was on Febrauary , i understand this is too late in uploading this content. But better late than never :)

In that session , I have presented on the below

1. Using Power Pivot.
2. How Power Pivot is similar to SSAS tabular Model.
3. Features in Tabular Model.
4. Demo on the Tabular Model.

Hope the session was usefull to whomever attended. All the contents of the session is available in the above link.

SSAS - Understanding Non Aggregate Measures

Recently I have been asked a question, I have defined my measure with aggregate function as “None” and all the values are returned as Blank. What is the reason for this? So I have created a below demo to explain the concept of “None” aggregate function and how it works. Most you may be having the same question. So I am documenting this here to help you to understand about this.

Below is the sample model which will be used for demonstrating this

I have created a cube using the wizard with the above data model.  Below is the structure of the cube

Dimension : Product 

Attributes :ProductId

Dimension : Region

                Attributes :RegionID

Dimension :Chk  (Dummy Dimension)

                Attributes :ChkId

Measures : Sum(SoldQty)

                BillNumber (No aggregate)

Measures :  Sum(Valx) (Dummy Measure)

The cube is deployed and processed. Now in the browser, let us take a look how this measure behaves.

Now when using Region Dimension alone measure BillNumber is returning blank.

Now let us try adding the Product Dimension also inside. But still we could see only blanks in the BillNumber.

But if you try expanding for each region you will start seeing the Billnumber values.

The reason for this is in the first two tries there was an attribute at the level “All” . As we have mentioned BillNumber as NoAggregate , there will not be any value available for a “All” member.

Now you may get a question that in the first screenshot we have used Region at the granular level only. But why BillNumber is still Blank ?  As RegionId has been used in the query , by default ProductId will be at the “All” level. In the below MDX query , the product dimension is not used in the query , But by default it “All” member has been  considered for getting the values.

But in the last screen shot , we have the leaf level of all the attributes available in the cube so the BillNumber measure displays the value.Does this means do we need all the dimension attributes in the query to display the no aggregate measure ? Not necessary if you notice we have one other dummy dimension DimChk and other dimension attribute Color also. Without using the chk dimension attributes they will be considered as All member. But still we are getting the BillNumber measure displaying the value.This makes clear that we need to have attributes which is having the IDs as keys which are linked to the fact table which has this measure. In the above example if we use the ProductID&RegionIDattributes , No aggregate measure BillNumber will display the value.

If we remove one of the key attribute values, the bill number will start displaying Blank.

Conclusion

No Aggregate Measures will display values only when attributes with all keys linked to the fact is available in the MDX query or browser as it displays the leaf level data.

I have uploaded the SSAS project and relevant scripts in the below link for your reference.

https://skydrive.live.com/redir?resid=EF9A407250207BDE!126&authkey=!AFraUYOCfPbhRrE

Hope this blog is helpful .Will meet you next week with another blog. So far I am happy that I could keep up my resolution on doing a blog per week and hope I will be able to keep it up JJJJ.

SSAS – How to create User Defined member properties

SSAS – How to create User Defined member properties

It has been a long time since I wrote a blog as I was so very busy with other priorities. It feels so better to be back into this. Hope I will be able to blog frequently from now on.

Recently I have encountered a question in MSDN forums with a question how to create multiple caption for a member due to the reporting needs. As this is just for reporting purpose I have recommended creating them as User Defined member property. When I went ahead and searched in the internet for an article to provide step by step of this I couldn’t find one. So I decided to document it here so that it will be useful for all.

What is Member property?

Member properties cover the basic information about each member in each tuple. For example you need additional information on a member in a tuple of State Province code on State- Province attribute in the Geography Dimension in Adventureworks. But you don’t need any aggregation on that. For this scenario you can create this State Province code as member property for the State province so that it can be used for reporting purpose and it will not take more space in the cube as there is no aggregation for this property. Now let us take the above example itself and see how this can be achieved step by step.

Steps to create a member property

     1. Open the Adventure works solution for SQL Server 2008 R2

     2. Launch solution explorer and open the Geogrpahy dimension which we will be using as an example in this step by step

 

    3. Right click on the State province code column in Data Source View section and select New Attribute from column. Instead you can drag and drop this column into the attributes section.

     4.The attribute State Province code will appear in the attributes section under the Geography. Right click on Stateprovincecode attribute and select properties.Also you can hit F4 to display the property window.

     5.On the properties window change the below property values

AttributeHierarchyEnabled	False
GroupingBehaviour	DiscourageGrouping

     6.Move to the attribute relationship tab , you will see by default State Province code is added under GeographyID. But we need to add this attribute as member property to State Province.

7.  Right click on the relationship in attribute relationships tab and say edit relationship. Change the Source attribute to State-Province.

     8.Now you can see the relationship changes reflected in the attribute relationship tab.

9.  With this the required changes are done and now we need to Deploy & process the cube.From the project à Build menu choose Deploy the cube.

    10.   Once the deployment and process is done , Double click on the cube and move to cube browser tab. In the left measure group pane , expand the Geography àState Province à Member properties. There you will be able to see the state province code added as a member property.

11.   Now you will be able to use this member property in your MDX queries as shown below

WITH

   MEMBER [Measures].[State Province Code] AS

  [Geography].[State-Province].Properties( "State Province Code" )

SELECT

   [Measures].[State Province Code] ON COLUMNS,

   [Geography].[State-Province].[State-Province]  ON ROWS

FROM [Adventure Works]

 

Conclusion

Member properties will be handy when only used for reporting purpose without any aggregation. These member properties will not occupy any space in the cube as there is no aggregation or indexing done on these properties and it will be stored with in the dimension. As side effect of no indexes on these attributes querying member properties may be slow. But for sure this will help in terms of processing and cube size.

Hope this article is helpful. Will meet you soon with one more article next week as I have resolved to write one article a week.

SQL Server "Denali" - SSAS Cube Browser - Query Design mode

In SQL Server Denali CTP3 , i noticed this cool feature Query Design mode in the cube. In the previous versions to get the MDX query for the attributes used in the report area we need to run the profiler to obtain the MDX query being generated. But in Denali , this has been made very simple by Query Design mode.

Click on the Query design mode on the tool bar and you will see the MDX query for the attributes and measures in the reporting area.

Also in this mode you can modify or write your own MDX query as like in the SSMS window.

This is really a cool feature which makes the developer life with below benefits

1. No need to run the profiler to get the MDX being designed.

2. MDX query can be written in the BIDS itself for testing the cube rather than switching to SSMS.

For more details , Refer to the below link

http://64.4.11.252/en-us/library/ms403829(SQL.110).aspx