Archive

Archive for the ‘SSAS’ Category

SSRS Report Parameter change does not refresh default values of dependent parameters Using SSAS cube as Data Source

February 14, 2016 Leave a comment

I have already resolved this issue inmy previous blog with SQL server as data source .

Now, I tried to resolve this using SSAS cube as datasource.

Follow below Video  link to see, how to do things to get the default value of parameter to refresh.

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: Home, MDX, SSAS, SSRS Tags: , , ,

SSRS parameters measures filters

March 12, 2015 Leave a comment

From MSDN I got this question  SSRS parameters measures filters 

This is the comment of user which he want to do.

For that I created 3 parameter
– the 1st parameter @Typeofmeasure lists the measures that I want to display. I have 2 values (Volume, Sales)
I created these values in specifying these values in the parameters properties

– The 2nd parameter @TypeOfAggregation lists the way that I want these measures to aggregate. I have 3 values ( Year To date, Month to date and Running 12 Month). I created these 3 values in specifying these values in the parameters properties too

– The 3rd parameter @Measures lists the measures that I want to display in my report.
So when user choose “Volume” in the 1rst parameter and “Year To date” for the 2nd parameter I want to have [Measures].[Volume YTD] in the 3rd parameter. Like a cascading parameter.

Before going to SSRS and MDX, please create the Calculated Measures in SSAS for Volume and Sales like Sales YTD , Sales MTD.

In my example I have two Measures and two calculated Measures

[Measures].[Sales Amount]

[Measures].[Order Quantity]

[Measures].[Sales Amount YTD]

[Measures].[Order Quantity YTD]

Lets come to SSRS report, create first parameter

Parameter 1 : – Typeofmeasure

Available values are as shown in below screen:-

Typeofmeasure

Parameter 2 : – TypeOfAggregation

Available values are as shown in below screen:-

TypeOfAggregation

Parameter 3 : – Measures

Available values are as shown in below screen:-

Measures

Measures parameter is cascading one, it has expression based on above two parameters

expression:

=“[Measures].[“+Parameters!Typeofmeasure.Value+ ” “+Parameters!TypeOfAggregation.Value+“]”

Lets create the main dataset for the report as shown in below screen.

MainDataset

MDX is like :

WITH MEMBER [Measures].[KPI]
as
strtomember(@Measures)
select  [Measures].[KPI] on 0,
[Order Date].[Calendar Year].[Calendar Year] on 1
from [Adventure Works DW2008R2]

Lets run the report

Case 1 Sales YTD

SalesYTD

Case 2 Quantity YTD

QuantityYTD

Note : Please keep the Common Name for the Measure in SSRS report so that  field Column will be fixed and it can be used in the SSRS report.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: MDX, SSAS, SSRS Tags: , ,

Creating Date Parameters in SSRS and pass it to MDX query

February 5, 2014 Leave a comment

Date Parameters in SSRS will open the calendar object, after selecting date it will pass to MDX as date format default in SSRS.

Let’s create one sample SSRS report with MDX.

Generate a report with birthdate as start and end date, based on these dates show employee names with gender.

Default MDX created by drag and drop in SSRS query designer for MDX as shown below:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Employee].[Birth Date].&[1955-05-07T00:00:00] : [Employee].[Birth Date].&[1985-05-07T00:00:00] ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

 1.DefaultMDX

If you see in above MDX the date is like its stored in the member 1955-05-07T00:00:00

 

Lets create parameters for this report.

Parameters created like in below screenshot:-

2.MDX-Parameters

 

Mapped the parameter in MDX . After modification MDX looks like

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( strtomember(@FromDate) : StrtoMember(@ToDate) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After completing wizard by clicking next à next it will generate report like below screenshot:-

3.DefaultReport

 

Date Parameters in above report is look like drop down not like usually we want as date calendar icon which should open as calendar and user can select date easily.

Now we need to modify MDX and parameters to provide the date calendar facility in the report.

Modify the MDX as below query:-

SELECT { } ON COLUMNS,  { ([Employee].[Empname].[Empname].ALLMEMBERS * [Employee].[Gender].[Gender].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( Strtomember(“[Employee].[Birth Date].&[“+@FromDate +”]”) : strtomember(“[Employee].[Birth Date].&[“+@ToDate+”]”) ) ON COLUMNS FROM [DSV_AD]) CELL PROPERTIES VALUE

After that please modify the parameters as shown in below screen shot:-

4.ParametersModified

 

If you run the report after changes it will throw error, modify the SSRS parameters also.

Create two more parameters which actually have the date data type.

@ BirthFromDate as shown in below screen shot

5.NewParameter

 

@BirthToDate as below screenshot:-

6.NewParameter2

 

So above created parameters will pass date as in the format to old parameters which are bond with the report

Format : 1946-06-13T00:00:00

Edit old FromDate Parameter and do as shown in below screenshot:-

7.OLD-Parameter-Modification

 

Expression pasted in above expression is

=cstr(Year(Parameters!BirthFromDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthFromDate.Value)<10,”0″+cstr(Month(Parameters!BirthFromDate.Value)),Month(Parameters!BirthFromDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthFromDate.Value)<10,”0″+cstr(Day(Parameters!BirthFromDate.Value)),Day(Parameters!BirthFromDate.Value)))+”T00:00:00”

Same expression should be pasted in default value of the parameter

Same procedure for Old parameter ToDate.

Onle change is the expression as given below:-

=cstr(Year(Parameters!BirthToDate.Value)) +”-” + cstr(iif(Month(Parameters!BirthToDate.Value)<10,”0″+cstr(Month(Parameters!BirthToDate.Value)),Month(Parameters!BirthToDate.Value)) )+ “-“+cstr(iif(day(Parameters!BirthToDate.Value)<10,”0″+cstr(Day(Parameters!BirthToDate.Value)),Day(Parameters!BirthToDate.Value)))+”T00:00:00”

Both old parameters should be hidden.

Run the report, you will find the report like below screenshot:-

8.FinalReport

 

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: Home, MDX, SSAS, SSRS

Perspectives in SSAS

October 9, 2012 Leave a comment

SQL Server Analysis Service after cube development we can use some advanced features to our cube.

Perspectives is one of the feature in SSAS. In cube designer there is one tab called  “Perspectives” where we can defined the required perspective.

Perspectives used as view , that means abstraction over available objects. In SSAS we have different objects like measures, dimension attributes, hierarchy etc. So if we define new perspective we can choose which all measures , dimension attributes, hierarchy we want to keep in newly created perspective. So once we define that user will see those many measures, dimension attributes, hierarchy which are available in the perspective.

This concept will be clear once we created the Perspective in SSAS.

Please follow the below link for more details

SSAS-Perspectives

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: SSAS Tags:

SSAS Database Deployment (XMLA / PowerShell)

September 25, 2012 Leave a comment

Deployment is the important step after development of any business solution.

Development of Business Solution is done on development box and then it moved to QA box for Testing and then to Production box where it can be used across business users. In all steps we need deployment utility to move the solution from one environment to another.

In SSAS also we need to move the solution from one box to other, for that we need to know the deployment ways.

Deployment Ways:-

  • BIDS Solution
  • XMLA

Please follow the below link for more details

SSAS-Database-deployment

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: SSAS Tags:

Usage based Optimization (UBO)

September 20, 2012 1 comment

Usage based Optimization (UBO) is special type of aggregation design scheme. We often create the aggregation in which we select the attributes role as default so at run time optimizer will select the attributes based on the characteristics and data in the attribute members.

In UBO model we log the user firing MDX queries into table , after logging the MDX quieries  UBO will get to know which all attributes are used frequently by the user. So while developing aggregation it will take those attributes in the consideration.

UBO model take time to mature and to produce valuable performance gain.

Let see how it can be defined or assigned UBO to particular partition.

Please follow the below link for more details

SSAS-Usage-based-optimization-UBO

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: SSAS Tags:

Aggregations -SSAS

September 18, 2012 1 comment

Aggregations as we know from the name itself the aggregate date. In last Post we discuss about the Partitions, when we create partitions at the end step it will ask for designing aggregations.

SSAS supports two type of aggregations.

Please follow the below link for more details

SSAS-Aggregations

More technical stuff can be found in http://msbitips.com

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Categories: SSAS Tags: