Wednesday, December 18, 2013

Hide multiple Measures in a Measure Group at once using Business Intelligence Development Studio...

Sometimes there will be situation that there are hundreds of measures in a measure group and we want to hide most of them except few measures.
But normally in the BIDS, measures are list down as a tree and in that you have to set the visibility one by one as shown in the below figure. Because we cannot select all the measure by this view.

But for hundreds of measures this will not be practical and it will take more time and effort. 
We can easily do this by changing the measures listing to Grid as shown below.

Then we can select the multiple measures at once using the Shift or Ctrl keys and set the visibility as shown below.

Hope this will be helpful and save your time and effort.

Wednesday, November 20, 2013

Exclude Unknown member from the TopCount in MDX...

While taking the Top or Bottom count using a MDX query or using a Named Set, we may need to exclude Unknown member. We can easily do it by using the FILTER function as below.

   1: TopCount
   2:     (
   3:     FILTER([Item].[Item].[Item].Members,[Item].[Item].CurrentMember.MEMBER_CAPTION<>'Unknown' ),
   4:     100,
   5:     [Measures].[Unit Price]
   6:     )

Same can be applied for the bottom count as well. Also we can filter any other member using the same approach.

Tuesday, November 19, 2013

Could not load type 'Microsoft.AnalysisServices.SPAddin.ASLinkFilePage' in SharePoint 2013

While trying to create a BI Semantic Model Connection, I got an error as Could not load type 'Microsoft.AnalysisServices.SPAddin.ASLinkFilePage'.
The reason is that the PowerPivot Web Application has not been deployed to the SharePoint Web Application.
We can fix this error by following below steps.
  •  Go to the SharePoint 2013 Central Administration site.
  •  Go to the System Settings section.
  •  Click on Manage Farm Solutions under the Farm Management section.
  • Click on powerpivotwebapp.wsp
  • Click Deploy Solution at the top of the screen
  • In the Deploy To section select your web application from the drop down list and click Ok.

Tuesday, November 12, 2013

SSAS Cube is not shown in Excel and other client applications...

We faced a situation that the deployed and processed SSAS cube is not shown in Excel and other client applications, but it can be browsed through SQL Server Management Studio.
Actually the reason was somehow the Cube Visible property is set to False. Cube will be visible for Excel and other client applications only if this property is set to True.
But if the cube has large volume of data, it will take more time to deploy and process the cube after changing the visible property. Therefore it will not be practical and will waste time and resources.

Therefore in such scenario we can use XMLA Query to alter the cube visibility property value.
We can easily get the XMLA Query by right clicking the cube and selecting Script Cube as, then ALTER To as shown in the below image.

Then in the query window go the end of the Dimension section and you can see that the Visible property is set to false as shown in the below figure.

Change it to true and run the query. Then browse through the Excel or any other application uses to browse the cube and you will be able to browse it.

Monday, November 11, 2013

Numbering data into specific number of groups using NTILE function...

Sometimes we may need to cluster data into particular group according to the particular value.
As an example as per the SQL Rank... secenario, we may need to distribute rows to 10 groups depending on the AvgRate value. 
For that we can use NTILE function as we used the Rank function. The difference in the NTILE function is that we need to provide the number of groups we want.

Sample query is as below

   1: SELECT YearKey, SupplierKey, ItemKey, NTILE(5) OVER (PARTITION BY ItemKey, YearKey ORDER BY AvgRate ASC) AS GroupByAverageRate FROM FactPurchases GROUP BY YearKey, SupplierKey, ItemKey

Thursday, November 7, 2013

Distinct Count in SSAS

In some scenarios we may need to get the Distinct Count value. As an example we may need to get the items purchased from a particular supplier. In such scenario we need to get the distinct items from the purchase orders, since the same item will be available in different purchase orders.
For that below expression can be used.

   1: DistinctCount(Nonempty([Item].[Item].[Item].Members, [Measures].[Fact Purchase Count]))

FactPurchase is our Fact table which stores Purchase Order data and Item is the dimension which keeps Item information. By default when we create a Measure Group in the Cube, it automatically adds the Count measure. In the above expression what it does is, it joins the Item Dimension with that Count measure and get the distinct Items from it.

SQL Rank...

There was a situation that Suppliers needs to be ranked by the lowest Rate given for the Items in a particular Year. It can be easily done by using SQL Rank function.
Sample query is as below.

   1: SELECT YearKey,SupplierKey,ItemKey,
   2: RANK() OVER (PARTITION BY ItemKey,YearKey ORDER BY AvgRate ASC) AS RankByAverageRate
   3:   FROM FactPurchases
   4: GROUP BY YearKey,SupplierKey,ItemKey

This will rank the Suppliers using the AvgRate of the Item available for the Year.

Tuesday, November 5, 2013

Get the Top N records for a result set in MDX

In SQL Query we can easily get the Top N records for a result set. But we cannot use the same syntax in MDX.

Below query will show the Top 10 records of the result set.

   1: SELECT {[Measures].[Unit Price] } ON COLUMNS,NON EMPTY
   2: Head(NONEMPTY(CrossJoin([Item].[Item].[All] ,[Company].[Company].[All].Children) ),10) ON ROWS
   3: FROM [Cube]

Friday, November 1, 2013

TopCount and BottomCount in SSAS

In some scenarios we may want to get the lowest or highest dimension attributes using SSAS Cube. As an example we may need to get the 10 items with lowest unit price and the highest unit price.

In such kind of a scenario we can use the TopCount and BottomCount functions.

To get the 10 items with highest unit price, below expression can be used.

   1: TopCount
   2:     (
   3:     [Item].[Item].[Item].Members,
   4:     10,
   5:     [Measures].[Unit Price]
   6:     )

Likewise I used the BottomCount in the same way to get the 10 items with lowest unit price.
   1: BottomCount
   2:     (
   3:     [Item].[Item].[Item].Members,
   4:     10,
   5:     [Measures].[Unit Price]
   6:     )
But it I filter data for the Month, it only shows the values for the last month. For others no rows were displayed.
Actually this is due to the NULL values and to avoid it, NONEMPTY function was used.

   1: BottomCount
   2:    (NONEMPTY([Item].[Item].[Item].Members,{[Measures].[Unit Price]})
   3:    , 10
   4:    , [Measures].[Unit Price]
   5:    )

This shows the 10 items with lowest unit price for the selected filtering criteria.

Tuesday, October 15, 2013

Cube Optimization in SSAS

In a BI solutions, SSAS cube may contains billions of records. In such large volume of data, query processing will be crucial. End user wants to get the output without much delay and to cater that you need to optimize the query processing. Otherwise it may take minutes to get the query results.

Anyway before optimizing the cube, DW must be designed properly to get the good query performance. In this post, only the cube optimization is considered.
For cube optimization, two main approaches can be used

  • Partitioning
  • Aggregation Designing


Since there are large volumes of data, we can split those data of a measure group, into different portions by considering different attribute values. Those portions of data set of a measure group are called as partitions.
As an example we can create partitions considering the month value. For each month there will be different partitions under one measure group. Those partitions are not visible to the end user and the user will only see one measure group.
Creating partition will help to process partitions separately and if only one partitions gets changed, then only that partition can be processed without affecting other partitions. Therefore partitioning is making the maintenance of the cube much easier.

Aggregation Designing

By using aggregation designing, Cube keeps pre-calculated set of data. It will increase the query performance, since those calculations are already done in the query execution.
We can design aggregation using the below two methods in SSAS cube.

  1. Design Aggregations Wizard 
  2. Usage Based Optimization Wizard

Both of above wizards can generated using the Aggregations tab of the Cube in BIDS.
In the Design Aggregation options, we can set the thresholds for the required aggregations and it will automatically design aggregations according to those thresholds.

Usage Based Optimization method uses the executed queries and design aggregations based on those. In that approach, aggregations are designed based on the frequently used queries, which takes more time.

For the long run, Usage Based Optimization will provide more query performance for the end user, since it will design aggregations based on the used queries.

Used the below reference to identify the steps used in BIDS for those aggregation design methods.
Design Aggregation Wizard
Usage Based Optimization Wizard

Friday, June 21, 2013

The job failed. Unable to determine if the owner (Domain\User) of job SSISJob has server access...

I created a SSIS package, which connects to the SQL Server database using windows authentication.
Package run successfully when I executed it in the BIDS and I tried to schedule a job to execute the package.

When I tried to run the job, I got the below error message.

The job failed.  Unable to determine if the owner (Domain\User) of job SSISJob has server access (reason: Could not obtain information about Windows NT group/user 'Domain\User', error code 0x5. [SQLSTATE 42000] (Error 15404))

To fix it what I had to do is set the Log On account of the SQL Server Agent to a domain account.
Normally it is set for a built in account.
Then check whether the Owner of the job is set to the same domain user as the Log On account.

This fixed my problem and I was able to run the job successfully. 

Monday, June 10, 2013

Tips to avoid empty pages and white spaces in SSRS reports...

Sometimes we find that there are white spaces or empty pages in the SSRS report viewer output or the PDF output. In this post I am going to mention the tips we can use to avoid those.
  • Set the ConsumeContainerWhitespace property of the report to True.
  • Set the body content to fit enough for the Report Page Width. Consider the margins too. Body width + Left and Right margins needs to be less than the report page width. Otherwise there will be blank spaces while exporting to PDF.
  • Remove unwanted space between the tables or charts in the report.
  • Check the page breaks, and disable those if there are no data by checking the Count of the data set.
  • Set the page breaks if you are setting the visibility of a particular section by using a parameter, by applying the same condition for the Disable property.
If those things were done properly, there will not be any unwanted white spaces or empty pages in the report.

Thursday, January 24, 2013

Ordering Sub Reports dynamically in SSRS by using parameter values...

There are some requirements that the order of the tables or sub reports needs to be changed according to the provided parameter values. I tried to find a way and searched the internet. But the found solution was add sub reports multiple times and show or hide those by considering the parameter value.
But this is not a good way because if there are 10 reports (tables) to be sorted, then we have to add all those 10 reports for each position and it will load 100 reports due to that.

I was able to get done this in a different way and it will provide better performance than this.
What I did was I kept all those tables in a single report instead of separate sub reports and show or hide those table by considering the passed parameter value. In that add a parameter as "Report" to keep the which table is need to be showed. This report is used as the sub report and in the main report what I did was create a data set considering the provided ordering parameter values.
Let say we have two tables and want to sort those table according to the given position.
For the first table, position is provided by the P1 parameter and the position of the second table is provided by the P2 parameter.
Then a dataset is created using those parameter values and the query is as below.

   1: SELECT        @P1 AS Position, 'Report1' AS Report
   2: UNION
   3: SELECT        @P2 AS Position, 'Report2' AS Report

In the data set just pass those parameters as below

Then add a table and set the DataSetName as the created data set and add sub report to the detail cell as shown below.

In the table, set the Sorting property of the Details group by the Position value in the created data set.
Also in the Sub Report pass the Report value in the data set to the report as shown below.

In the sub report which contains those two tables, set the visibility of those tables by considering the "Report" parameter value.
Also to avoid loading the unwanted dataset data, we can only execute the query by checking the "Report" parameter value and if only it matches with the relevant table.
This will improve the performance since only the required data is retrieved and does not load all the data sets.