Archive

Posts Tagged ‘SSAS’

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

Advertisements
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: , ,

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:

Partitions -SSAS

September 18, 2012 1 comment

Partitions comes in one of the step towards SSAS cube performance tuning.

SQL Server tables we do the partitions for better query performance or we can said dividing the table space in logical manner we can retrieve back easily by looking into such blocks.

SSAS also provide partitions to be created on cube database.  Analysis Database is also grow as the underline relational database grown. To improve the MDX query performance we do create the partitions.

Business Intelligence Development Studio (BIDS) we  have the cube , when we double-click on the cube it will show different tabs, like Cube Structure, Dimension Usage etc, Partitions tab is also there which is shown in the below snapshot.

Please follow the below link for more details

SSAS-Partitions

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: