Uncategorized

Creating solution file from a SSAS database

Creating solution file from a SSAS database

In many of my projects, I have heard my developers asking each other whether it is possible to get the solution file of a SSAS database and I have turned a deaf ear to almost all the cases in spite of knowing the answer. Now before you give me that flabbergasted look, let me try to justify my position. As an IT company, we do have certain processes and one of them is to keep your code in VSS (used for version management). Unless you follow this rule, there is every chance that you might miss upon a particular Change Request / functionality and end up implementing a new fix on an outdated version. And it is not common to see people doing their development work on the online version of SSAS (which is the SSAS database) because it is much easier. You just have to save your work to see the changes while if you need to do the same changes in the offline version (which is the solution file), you will need to save it and then deploy to see the changes (talking about changes like modifying the calculated members script. For most other changes, the cube has to be processed for the changes to be reflected). So I would rather not let them know the answer so that they are extra careful in the future and avoid making changes in the online version, which may just be executed with the help of pdf to word converter.
But sometimes, in support scenarios, it is necessary to know this technique. Let’s say, you have been supporting an application which has been live from the past 5 years and now a change request has come in. Now let us face the ground reality, the application has changed hands so many times that you don’t know how many vendors have been involved nor do you know where the latest source code is. At this time, the best option is to retrieve the source file from the online version and this post will show you exactly how to do that.
1) Go to Start–>Programs–>Microsoft Visual Studio 2005 and click on Microsoft Visual Studio 2005 which will launch the IDE (If your SSAS database is 2008 version, use Microsoft Visual Studio 2008 instead)
2) Click on File–>New Project
Select the Import Analysis Services option and after giving the required Name, Location and Solution Name, click OK.
3) A Wizard message will pop up prompting to continue by clicking Next. Click Next and the fill in the server name as well as the DB which you need to import. Click on Next after that
4) The Wizard will start importing the source files from the database and on completion, the Finish button will be activated. Click on Finish.
5) Presto! You have the source files right in front of you.
Disclaimer: Use with caution, Tech Lead advice recommended. I don’t want companies coming after my neck just because my blog meddled with their processes 🙂
Posted by SQLJason, 3 comments
Report selection in parameter toolbar

Report selection in parameter toolbar

The MSDN forum has been the inspiration for most of my blogs, but in all those cases, I was sure that I could attain the end result and my blogs were usually a graphical step by step depiction of what needs to be done, so that I could post it as an answer to the question. But this blog is quite different, I almost wrote a no saying that it is not possible. And that was when I decided to give it a try before replying No 🙂 To give a background of the problem, the user wants to display the list of reports in a dropdown list and on pressing the view report button, the selected report must be displayed. On reading this question, I started typing – no, it is not supported by design in SSRS and suddenly I thought whether I am doing any value-addition to the post by typing this answer, I might as well as not write it and wait for some expert to confirm it. So I just thought of trying it while some expert gives his answer, and by chance, I stumbled upon a workaround. To implement this, follow the steps below:- 1) Create a report parameter (say RP) and in the report parameter properties, specify the default values. The labels should contain the report names and in the values, use integers as shown in the figure below RP Properties 2) Create a matrix in the layout and for each report add a row. In this example there are 3 reports hence add 3 rows. 3) For each of the row, drag and drop a subreport  from the toolbox into the data cell. Then right click and select the subreport properties. Subreport properties In the General tab, select the required report from the dropdown list which contains the list of reports (as shown in the figure above). Press OK. 4) Once you have modified the properties of all the subreports, select the entire row and right click on it. Row visibility Select Row Visibility option. 5) Select the Show or hide based on an expression radio button Setting row visibility expression For the first row, enter the following expression:- =iif(Parameters!RP.Value="1",False,True) This expression will ensure that the first row will only display if the parameter value is 1, else the row will be hidden. 6) Repeat steps 4 and 5 for each row. Substitute the number 1 by the appropriate value in the expression specified in the 5th step. 7) In the matrix column header, use the below expression to display the name of the report =Parameters!RP.label 8) Save and deploy the reports. Now you can preview the report in internet explorer, and when you select the report name from the parameter dropdown and click on view report button, the appropriate report will be displayed. order count by product Image visibility Rich text This technique can be extended to pass other report parameters also by passing the required parameters to the subreports. Don’t you think I deserve a pat on the back for this? 😉

Posted by SQLJason, 4 comments
SSRS reports using embedded images

SSRS reports using embedded images

Ok, this post is dedicated to all those zillion questioners asking how to use embedded images on your report based on a condition. Yet again, I will be making use of my favourite dataset from AdventureWorks – Order Count by Product (people who are following me would understand that this is the same dataset that has been featuring in many of my blogs. I admit it guys, I am too lazy to make a new dataset. On second thoughts, this doesn’t look  that bad, considering I have created a re-usable component in the form of my dataset. Woah, high fives!) For making this report, I am going to make use of three images mainly:-

a) Home                  b) Thumbs Up        c) Thumbs Down

Home                 thumbsup               thumbs_down

The home image would just be used in the top left of the report, which could be used for hyper linking to the home page. The other 2 images would be used in a matrix, and would be displayed based on a condition. (P.S. : This report is made in SSRS 2008, although the steps are quite similar in SSRS 2005 also) To make the report, follow the steps below:-

1) On the Report Data panel, go to Images and right click on it. Click on Add Image and select the images you need to embed by giving the source path.

Add Image

2) Once you have uploaded the 3 images, drag and drop Home image from the report data to the body of the report and press ok on the popup that appears (This is one method to bring embedded images in your layout)

3) Make a matrix underneath the Home image like shown in the figure below

Tablix

4) Now the matrix will be having Products on it’s rows and order counts on it’s columns. Now let us add the Thumbs up image for the row which has the highest value of Order Count and the Thumb down image for the row which has the least value for Order Count. For this, go to the toolbox and drag and drop the Image tool to the vacant column in the right end of the matrix (This is the second method to bring embedded images in your layout). You would be getting the Image Properties popup on doing this. Click on the expression symbol in the image name.

Image Properties

5) Once you get the Expression editor open, enter the following expression:-

=iif(Fields!Order_Count.Value=max(Fields!Order_Count.Value,”DataSet1″),”thumbsup”,

iif(Fields!Order_Count.Value=min(Fields!Order_Count.Value, “DataSet1″),”thumbs_down”,””))

where thumbsup and thumbs_down are the names of the images embedded in the report. Notice that they are enclosed in double quotes. Also notice that if none of the conditions are true, empty string would be displayed.

6) Save the report and deploy it. Now you should be able to preview the report in internet explorer.

Image Visibility report

This is all it takes to embed images in your reports and use them. You can use them for a wide variety of purposes, though I usually use them for showing indicators or for using them as hyperlinks. However, if you want to store images in your database and use them in your reports, you might want to check out this post.

Posted by SQLJason, 24 comments
Adding breadcrumbs to your drillthrough reports

Adding breadcrumbs to your drillthrough reports

In one of my projects, I was asked to implement a drillthrough report, which had a depth of around 6 pages. After the development, I was sitting with the user who was testing the reports and I noticed that the user was frequently shifting between the sixth and the third report to change the values. As most of us, she was also just using the back button of the internet explorer 3 times to reach the third report from the sixth report. That was when I quizzed her whether this was going to be a common requirement and suggested the idea of using breadcrumbs as a navigation aid. Though I can’t show you the original report, I will illustrate the idea here by making some sample reports. Here, I have created three chart reports using the AdventureWorks database to simulate the drillthrough effect – a) Order by Category Design Mode - Order by Category b) Order by SubCategory Design Mode - Order by SubCategory c) Order by Product Design Mode - Order by Product The Order by Category will be used as the starting point and on clicking a particular category in the chart, it will drill down to the subcategories of that category. Similarly, on clicking a particular subcategory, the chart will drill down to the products of that subcategory. Now, follow the steps detailed below to add the breadcrumbs:- 1) Now in the first report, add a textbox with the text – Order by Category as shown below Text in Order by Category report 2) In the second report, copy the same textbox and just to the right, make another textbox with the text – Order by Subcategory and do the formatting as shown below Text in Order by SubCategory report 3) Right click on the first textbox, select the textbox properties and click on the action property. Now click on the Go to URL radio button and enter the following expression =”javascript:history.go(-1)” Setting the action property for breadcrumbs This particular javascript will retrieve one page back in history. 4) Similarly, in the third report, add the textboxes in the format shown below. Text in Order by Product report 5) Edit the action property of the first textbox (as shown in step 3) and enter the following expression =”javascript:history.go(-2)” For the second textbox, enter the following expression =”javascript:history.go(-1)” 6) Save and deploy all the three reports and view them in internet explorer. Order by Category Order by SubCategory Order by Product As you can see from the images below, the breadcrumbs would be displayed. The active links would be displayed in blue while the inactive (the present page) would be displayed in black. You can click on the breadcrumbs directly to navigate between pages. This makes the drillthrough reports more user friendly and professional. The user for whom I designed this was extremely happy with the end result and gave me a BIG hug for making her life easier. As for me, I have included this trick in my list of best practices when designing drillthrough reports, who knows whether I would get another hug some other day by implementing this again 😉 Title

Posted by SQLJason, 1 comment
Rich text in SSRS 2008

Rich text in SSRS 2008

One of the most irritating and common requirements that I faced as a developer in SSRS 2005 was when I had to colour words in the same line differently. Even though SSRS 2005 didn’t provide an inbuilt feature to do this, this could always be done by placing the words in different textboxes. Suppose I had a title as Category = Accessories and Order Count = 19523 where Accessories and 19523 are retrieved from the dataset, and if I just wanted them in a single colour, I could write the below expression in a textbox. =”Category = “+Fields!Category.Value+” and Order Count = “+Fields!Order_Count.Value The hassle was when the customer gave the requirement that the values should be in a different colour and in bold. Now for this, we need to break the expression into different parts such that only consecutive words of the same colour appear in one textbox. Hence, the above expression had to be broken down into 4 textboxes:- 1) Category = 2) Fields!Category.Value 3) and Order Count = 4) Fields!Order_Count.Value and then each textbox could be given it’s own property. simulating rich text in SSRS 2005 This would prove to be very annoying for the developers especially if the sentence was a very long one which had alternating colours in between. Preview of rich text in SSRS 2005 Also, unless you painstakingly spend hours in it, the spacing would not be 100% accurate. For eg, if you see in the above image, you can notice that spacing between the words is not uniform But with the advent of SSRS 2008, this issue is a matter of the past. Now you can directly type in your sentence and/or drag and drop the fields from the dataset or the report parameters. To change the properties of the required text, just highlight the part and then press F4 to view the property panel of the selected text. Rich text in SSRS 2008 In the example above, I have highlighted the [Category] text. Notice how the property panel displays Selected Text, and not the name of the textbox. Now you can change the properties as you like be it the colour, indentation, font, etc. Another thing to notice is that instead of displaying <<Expr>>, we can actually see the words and the Field names have a placeholder. Eg, for Fields!category.Value, we actually see [Category]. Also see below how neat the result looks compared to what we had in 2005. Preview of rich text in SSRS 2008 This can be extended to objects like tables and matrixes also. In SSRS 2005, if it was required to have a column partially bold or coloured, we had to make different textboxes and then enclose them within a rectangle. Then the rectangle could be used in the matrix. Table in Design mode - SSRS 2005  Table in preview mode - SSRS 2005 In SSRS 2008, you can just select the required text and edit as mentioned in the previous section. Matrix in Design Mode - SSRS 2008 Table in preview mode - SSRS 2008 It is small features like this which makes me an ardent fan of SSRS 2008.

Posted by SQLJason, 7 comments
Searching substrings in MDX

Searching substrings in MDX

A quick tip for the beginners. Most of you would be familiar with substring searches in SQL. Today, we will see how to replicate the same scenario in MDX.

1) Suppose we have to find all the employee names having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on COLUMNS, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)>0) on ROWS from [Adventure Works]

Query result

2) To find all the employee names not having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName NOT LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)=0) on ROWS from [Adventure Works]

3) You can write multiple conditions also. For e.g., to find all the employee names having ‘David’ as a substring but not having ‘am’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount] FROM Employee WHERE EmployeeName LIKE ‘%David% AND ’EmployeeName NOT LIKE ‘%am%’ 

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns, filter([Employee].[Employees].allmembers, instr([Employee].[Employees].currentmember.member_caption,’David’)>0 and instr([Employee].[Employees].currentmember.member_caption,’am’)=0) on ROWS from [Adventure Works]

Update (25/08/2010)

You can also do the same using Stored Procedures. You can create your own Stored Procedures to achieve this or you can also download Analysis Services Stored Procedures project from:
http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures

After that, you can use the code like this:

SELECT 
{} ON 0
,ASSP.Like([Employee].[Employees].Members
,”%RA%”
,[Employee].[Employees].CurrentMember.Name)
ON 1
FROM [Adventure Works];

Update Courtesy – Charles Wang (MSDN Moderator)

Title MDX

Posted by SQLJason, 1 comment
Highlighting threshold values in a chart

Highlighting threshold values in a chart

One of my favourite activities is playing around with the charts in SSRS and trying to tweak their properties. Knowing my penchant for this, one of my colleagues asked my help in verifying whether it is possible to have charts where the columns will have a different colour based on a certain threshold value. From the moment I heard it, I knew it should be possible but then I didn’t want to end up in a “You-told-me, now-you-solve-it” situation at the end of implementation. And also, I didn’t want to end up in the bad books of a pretty lady (Oh, did I forget to mention that my colleague is a gorgeous woman? 😀 ). So I thought of giving a quick try to confirm. For implementing this functionality, I used the Adventure Works R2 analysis services database to create a column chart with Order Count measure on data and Category from product dimension on the categories part. Now, click on the column and select the dropdown in the Color property as shown below

Setting the color property

Now, click on the Expression and then give the conditions for which the threshold value should be highlighted, lets say the maximum value for Category should be Green while all others should be Maroon.

=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,”Maroon”)

And now, when you preview it, the Category with the highest value would be highlighted in Green.

Chart highlighted with Green for max value

You can also modify your expressions to highlight both the max & min value, or just highlight all columns above or below a particular value.

=iif(Sum(Fields!Order_Count.Value)=max(Fields!Order_Count.Value, “DataSet1″),”Green”,iif(Sum(Fields!Order_Count.Value)=min(Fields!Order_Count.Value, “DataSet1″),”Red”,”Maroon”))

Chart with max and min values highlighted

=iif(Sum(Fields!Order_Count.Value)>10000,”Green”,”Maroon”)

Chart highlighted with green above 10000

 

Posted by SQLJason, 0 comments
Different ways of referencing objects in MDX

Different ways of referencing objects in MDX

Back to the basics today. Often, I have been asked by colleagues what are the different ways of referencing objects like Dimension, Hierarchies, Levels and Member names in MDX. Most of the times, I have seen people following the way they learnt from their seniors or simply the first way they found to be working without understanding the intricacies. This post is meant to shed some light on the basics. There are 3 main ways of referencing objects in MDX, namely

  1. By Name :- This is the most easiest way of referencing an object, by just specifying the name. For ex., if you have a member Australia in the level Country, you can just refer to it by specifying Australia.
    • Comments :- If you have multiple objects with the same name, for ex., having Australia in 2 dimensions, then the result would be ambiguous. Also, it hits the performance badly as all the dimensions and hierarchies have to be iterated to resolve the member name. Moral of the story – you might not want to use this approach unless you want to get fired by your boss. This is the worst way to reference an object in MDX.
  2. By Qualified Name :- For a dimension, the qualified name is equal to the name of the dimension in square brackets. Ex. [Time] for time dimension. For a hierarchy, it is the the dimension name followed by the hierarchy name in the following format – [Time].[Calendar Hierarchy]. For the level, it is the qualified name of the hierarchy followed by the level name in the format – [Time].[Calendar Hierarchy].[Year]. For a member, the qualified name is the qualified name of the level or hierarchy followed by the names of all parents of the current member and the name of the current member -  [Time].[Calendar Hierarchy].[2009].[Q2].[May].
    • Comments :- This method is faster when compared to the previous method and works well in most cases. The only issue with this is that if the qualified name for the member is created by concatenating all the parent levels, then the qualified name becomes immobile. It will get outdated the moment a child changes it parent and it can happen especially in cases like where a customer changes his city.
  3. By Unique Name :- Analysis Services assigns a unique name to every object and this can be retrieved by using a schema rowset or from the results of another MDX request. Usually, the unique name is generated by using the member key (rather than the name like in the previous 2 methods). For ex., if the key for 2009 was 1412, then it would have been referenced as [Time].[Calendar Hierarchy].&[1412].
    • Comments :- This is the most correct way of referencing a MDX object. However care should be taken that the unique name should never be generated by the MDX programmer, and should always be retrieved from the server. The reason is that the generation of unique names is a complex task and the providers that support MDX may have different algorithms for generating unique names. Also, the rules might change from version to version and hence, to make sure that the application we build is compatible with the next version, never generate unique names on your own.

Title MDX

Posted by SQLJason, 2 comments
Implementing measure security in SSAS 2008

Implementing measure security in SSAS 2008

Recently, I was asked to implement security on a set of sensitive measures, such as profit and all calculated measures on top of them as part of the cube development. It looked like a very straight requirement and I never thought I would have so many sleepless nights over it for the next 2 months. My very first way of thinking was using the default Microsoft cell security, where I would define a read-contingent policy for the sensitive measures. If you do not define an explicit read-contingent policy, the role will be able to read the derived measures of the sensitive Profit measure despite the fact that it does not have access to the Profit measure.

Img_Blog1

This led to 3 main problems for me:-

1) Reports which are running normal under the PowerUser role go berserk when running under the restricted RegularUser role. Empty rows started appearing in spite of having NON EMPTY in the MDX queries. Currently, I am following this issue with Microsoft and this is the latest reply I have got ” We have figured out the problem is caused by the cell security in the cube. When you set the “Enable read permissions” in the cell data, the non empty function doesn’t work for the special role. All the null records still display even you are using non empty. For the admin role, the non empty can filter out all the null records. For example, when you query the cube by the scripts below, you can get 42 records without null by PowUsers. If you are using the role RegUsers, you can get 132 with a lot of null. “

2) Performance went for a toss, and reports run under the RegularUser role started taking much longer time than when it was run under the PowerUser role. Later, I found that this could be mitigated with a technique given in this book (a definite must read for Expert cube designers, gives the inner details of SSAS like no other book has given) which I will try to put in another blog.

3) Even though the restricted measures were showing as #N/A in the cube under the RegularUsers, the reports started throwing an error when I just expected the #N/A again.

There was a requirement for a report which had the sensitive data along with some 5 other tables and all other data needed to be visible to the RegularUser. So time for a workaround. Luckily, I got guidance from Raymond Lee, MSDN forum moderator and came up with this approach.

1) I already had a dimension called Product. Added a dummy attribute to the key of Product dimension, and called it as RoleSecurity. The value of this attribute would be 1 and this attribute is hidden in the product dimension.

2) I needed to have security access on the measure Profit. I renamed that measure to Profit Original and made it hidden. Then, in the calculated members of the cube, I created the following calculated member

CREATE MEMBER CURRENTCUBE.[Measures].[Profit] AS IIF( ISERROR ([Product].[RoleSecurity].[1]), 0, [Measures].[Profit Original]);

3) I have 2 roles called RegularUsers and PowerUsers. PowerUsers have access to all measures, so no need to do anything there. For the RestrictedUsers role, select the Dimension Data tab and then use the “Deselect all members” for the RoleSecurity attribute. This implies that the users of this role will not have access to any of the values of this attribute.

Img_Blog2

Once this is deployed, if a user of RegularUser role tries to view the Profit measure, he will see 0 as the result, while the PowerUsers will see the actual value. In the reports, the 0 can be used to conditionally display a message like “Not authorized” if needed.

RegularUser                                                 PowerUser

Img_Blog3 Img_Blog4

The advantages of this technique is that

1) Since dimension security is used, it doesn’t affect performance like cell security does.

2) Reports will not throw up error even if the measure is missing.

3)Most importantly, the issue of null rows is solved

The drawbacks are

1) The Profit Original measure is not secured, jut hidden. If the user becomes aware of the measure and also knows how to use MDX well, he will be able to query it out.

These are the advantages and disadvantages I can think of. In any case, it solved my issue because my business users don’t know MDX and will not be using any tool to query. It would be great if I get to know your views on this technique. Here is the link for the forum post regarding this.

Posted by SQLJason, 5 comments
SSRS reports using database images

SSRS reports using database images

In one of my first projects, I was asked to store images in database and then use them for my SSRS 2005 reports. The newbie I was, for a moment I was shell-shocked how I could store images in a database, let alone use them in my SSRS reports. Fortunately for me, I mumbled to my client the most used phrase in the IT indutsry that I will check with the team and let him know, rather than showcasing my ignorance. A couple of searches and I had got all the matter that I required. This post will show how to create a sample employee report based on database images.
1) To start, create the employee table and populate the data into them. Sample code has been given below but remember to give the location of the photos.
CREATE TABLE EmployeeProfile( EmpId INT , EmpName VARCHAR (50) NOT NULL, DeptName VARCHAR (50) NOT NULL, EmpPhoto VARBINARY (MAX) NOT NULL);
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1001, ‘Prashant Kumar’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Prashanth.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1002, ‘Jitain Raheja’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Jitain.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1003, ‘Sandeep Shetty’, ‘Manufacturing’, BulkColumnFROM OPENROWSET (BULK ‘C:Sandy.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1004, ‘Rahul Nair’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Rahul.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1005, ‘Hari Selvarajan’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Hari.jpg’, SINGLE_BLOB) AS EmployeePicture;
2) Create a report with 2 datasets and one report parameter
a) DS_RP Query : SELECT DISTINCT DeptName FROM EmployeeProfile
b) Image Query : SELECT DeptName,EmpId, EmpName, EmpPhoto FROM EmployeeProfile WHERE (DeptName = @RP1)
3) Make sure that the properties of report parameter RP1 is set as follows
4) Go to the layout and then drag and drop a table from the toolbox. In the detail row of the first column, drag and drop an image. Then follow the steps of the image wizard.

Once the database option is selected, click on next and select the datasource, image type and image field from it.

Click on finish.
5) On the other 2 columns of the table, use the Emp Name and the Emp ID.
6) Click on preview. With a bit of formatting, you should be able to attain better results.

On selecting DW

On selecting Manufacturing

 
Posted by SQLJason, 4 comments