Year: 2010

Drill down in SSRS Map Reports

Drill down in SSRS Map Reports

December 19, 2010

I have been always interested in the potential of map reports. So naturally, when I found out a site which had quite a number of shapefiles for free, I decided to blog something just for the fun of working with map reports. Being around in the forums, I had seen a lot of posts asking how to drill down or zoom to the next level in map reports and I always had to explain the answer in words. I decided to take this opportunity to blog down the answer with some pictures. To implement this, I downloaded the shapefiles of India and followed the following steps :- 1) For the demo, I have used the administrative level 1 (States) and administrative level 2 (Districts) of India. The 2 above mentioned shapefiles are imported into the database (to see how to import shapefiles to tables, refer to Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)) as the size of the shapefiles was around 2 MB and that would slow down the report. 2) Make a basic map report for the States with the source of spatial data as a SQL Server spatial query. State report The above report shows colouring by sales for each state. The dataset used for this report is shown below SELECT NAME_1 AS State, Sales, geom AS Geo
FROM     IND_adm1 Dataset The above query returns all the states for India. 3) Make the second report which shows a similar report for the next level. The dataset used for the query is given below SELECT NAME_2 AS District, Sales, geom AS Geo, NAME_1 AS State
FROM     IND_adm2
WHERE  (NAME_1 = @PAR_State) Dataset Districts The important part to note here is the WHERE condition. This query returns all the districts for the state which is equal to the report parameter. Since this query is used as the source of the map, the map report will automatically show just the selected state. 4) Make a report parameter PAR_State in the second report and give a default value of any state, for eg, Kerala. Check whether the report is working by previewing it. Kerala 5) Go back to the first report and specify an action to the second report. For this, right click on the map and select Polygon Properties. 1 Action - Polygon Properties Then go to the Action tab and pass the State field to the PAR_State report parameter as shown below 2 Action - Report parameter passing 6) Click on OK and deploy both the reports. Now you should be able to click on the states and drill down to the districts as shown below. Screen Captures Now this might not be the only way to achieve drill down in map reports but I hope this gives you an idea on how to further play around with it and get your results. Meanwhile, for those extra attentive guys who must be wondering how I managed to change the background images for each state, I have uploaded a different image for each state in the database (No, I am not getting paid by the Indian Tourism Board for the extra effort Smile) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in advance…

Posted by SQLJason, 3 comments
Subscripts and Superscripts in SSRS Reports

Subscripts and Superscripts in SSRS Reports

December 6, 2010
               Two weeks back, I was reading an insightful article by Thomas LaRock – Delivering a Great Presentation. A couple of lines kept on lingering in my mind, mainly
– “Storytelling is a very powerful way for you to communicate ideas because they are such a powerful way for people to learn”
– “There are five stories that make up effective presentations. Every great story falls into one of these five:- the Quest, the Stranger, the Love story, the Rags to Riches and the Revenge story”
The more I thought about it, the more I could relate the same to blogs. I always tended to remember more articles or facts which were woven around a story than those which were plainly narrated. And also, I found that most blogs which I liked, if not all, could be categorized into the above said sections. Well, today’s blog is going to be the story of a quest, something which I had tried to achieve months before, lost hope and abandoned it, and then again renewed efforts and finally, found the solution – how to use superscripts and subscripts in SSRS reports.
 
It all started with one of my colleagues challenging me to write a chemical equation and a mathematical equation together in SSRS. What I failed to understand when I took up the bet was that it was a cleverly disguised challenge to implement superscripts and subscripts in SSRS. The challenge was to display the following in SSRS:-
 
Chemical Mathematical equation
 
Five minutes in BIDS and I understood that I was trapped. All I could remember next was me frantically pacing through all the search engines racing against time and a flurry of web pages opening. But apart from this link in the SSRS forum, I couldn’t find much help and I had to concede defeat. Laden with a bruised ego and a lighter pocket, I decided to delve more deeper into it. After quite a bit of research, I found 2 ways –
 
Solution 1

1. Note down the following codes Superscript and subscript codes 2. Use expressions along with the chrw function in textbox. Eg:- =”a”&chrw(8304)&chrw(185)&chrw(178)&chrw(179)&chrw(8308)&chrw(8309)&chrw(8310)&chrw(8311)&chrw(8312)&chrw(8313) Expression builder 3. Make sure that the font is Lucida Sans Unicode. Font 4. Now you can preview the result. Preview report  

Solution 2
 
1. Click on Start→Run and type charmap.exe. Click OK.
Run window
 
2. Change the font to Lucida Sans Unicode and then find the character that you need. For eg, Subscript Two is selected in the image below.

Charmap Once that is selected, click on Select and continue writing your chemical/mathematical equation in the same way. 3. Once that is done, click on copy button (and not Ctrl+C or any other shortcut button) and all you have to do is to paste it in a textbox in the report. Make sure that the font is Lucida Sans Unicode for the textbox. Textbox with chemical equation 4. Click on preview and you will have the equation that you wanted. Preview report with equation Note : You can select any character in the Character Map and then use the chrw function. For that, note down the number in the character map (for eg, in the image above for Step 2, the number for Subscript 2 is 2082) and convert it from hex to binary (You can use sites like this for converting it if you don’t want to do the maths) and then use it within the chrw().

Posted by SQLJason, 3 comments
Conditional Navigation in SSRS

Conditional Navigation in SSRS

December 1, 2010

Why do people write blogs or help other people in the technical forums? This was a frequent question that I used to ask to people around me. For some reason or the other, I couldn’t fathom why people would want to spend their personal times on such stuff when they could spend the evening with their family, having a drink with friends or simply take a stroll (not to speak of hard core partying at a nightclub, common, you can at least watch the girls if you don’t have the guts to ask them out! Hot smile). Modern life seems to have spoiled us with options. Even though I used to ask questions in the Microsoft forums, I had always been a “take” person rather than a “give” person. Just to experience this feeling and to get my answers, I decided to take the plunge into the forums as well as the blogosphere around one year back. Now, looking back, I am happy that I did take this step and could give many reasons – making new friends, networking with peers and staying updated on the latest developments, the joy that you get in sharing,etc but if I was asked to name one most important aspect, I would say it as – the opportunity to learn new things. You get exposed to thousands of people and their project related problems, you get to think on those issues and experience it first-hand which is almost as good as getting the experience of those thousand projects. Just to give an example, Jamie Thomson (MVP) had recently asked a question in the SSRS forums on how to conditionally turn on an action in the reports. This looked like a piece of cake until the requirements became more specific (don’t know why I feel like drawing an analogy to the projects during estimation and the same projects during development Smile ) To tell you more about this, Jamie wanted to use an action to launch another report conditionally when clicking on a textbox. To simulate this, I have come up with a simple report and the following steps:-
1) Design a simple report with a datetime report parameter. In the layout, have a textbox which will display only the date part of the report parameter as shown below. 1 Sample Report 
2) Right click on the textbox, select properties and go to the navigation tab. Then select the Jump to URL radio button and enter the following expression =iif(day(Parameters!Report_Date.Value)=”19”,”http://www.google.com”,””) 2 Navigation property  The above expression basically enables the hyperlink to the specified site if the Report_Date report parameter is 19, else it disables the hyperlink because the blank string is passed. Note : Care should be taken that the URL is prefixed with http. The URL of Google can be replaced by any valid URL including the report URL Eg:- http://servername/ReportServer?/Folder/Report%20Name&rs:Command=Render 3) Now, if we preview the report, we will be able to see that the hyperlink works as expected. If 19 is selected, then the hand symbol appears when we hover on the textbox indicating that it is hyperlinked, else only the arrow icon appears indicating that it is not hyperlinked. 3 Test functionality  Now this should work well in most of the cases because usually all the reports would be viewed online, and hence they should have been deployed to the report server. So whenever we need to navigate to a particular report that is deployed online based on a condition, we can use this technique. But the requirement here was that Jamie needed to show the reports in SQL Server Management Studio (Read this link to know how to create custom reports in SSMS) and hence there was not going to be any report URLs. 4) In this case, there is only one option which is to use the expression builder for Jump to Report instead of Jump to URL. In the expression, we can specify the report name directly. So if we have to conditionally navigate to a report called MyReport, we would expect the expression to be =iif(day(Parameters!Report_Date.Value)=”19”,”MyReport”,””) This had two issues – first, the hand symbol would always be present even if the condition is false, and secondly the following error would throw up on clicking the textbox when the condition is false (that is, when the empty string is passed) “ An error occurred during local report processing. Report / cannot be compiled. Verify the report name and that the report is in the current project “ 5) After fiddling around with the expressions, I found out that the following piece of code would work =iif(day(Parameters!Report_Date.Value)=”19”,”MyReport”,0) Yes, we just have to replace the double quotes with a constant like 0 and then we can find out that the hyperlink works as expected based on the condition. If it was not for the post in the forum, I might have never stumbled upon this learning. It is small things like this which makes me addicted to the forums and blogs. No matter how less experienced a person is, I believe there is always something to share because the individual experiences would be always unique. And this is the very reason why I always encourage my peers to write blogs and share their knowledge. And well, if you are still on two minds whether to write a blog and contribute, read BI expert Vincent Rainardi’s take on Why write a blog? I hope it will give you the final push to start your own blog.Thumbs up

Posted by SQLJason, 1 comment
Is it the end of the road for SSAS?

Is it the end of the road for SSAS?

November 12, 2010

If you have been following the PASS summit notes coming from the attendees, you would have noticed an unmatched sense of pandemonium in the SSAS community. The reason is none other than the announcement of Microsoft’s decision to move from the traditional multidimensional SSAS database to the new in-memory Vertipaq BISM. My first source of this news was from Chris Webb’s blog – PASS Summit Day 2 and after reading it, I was in a state of shock for quite some time. It seemed hard to digest that Microsoft could be doing this to one of their most successful tools in the market. This could have been aggravated by the realization that one of my core skills was going to get obsolete and the hesitation or resistance to change from my comfort zone. Even Teo Lachev seemed to mirror the moderate disappointment that seemed to be floating around in Chris’ writeup in his own blog – The Battle of the Models. It wasn’t disappointment everywhere as experts like Boyan Penev (Thoughts on BISM, SSAS and MDX) and Siddharth Mehta (SQL Server Denali – Boost to SSIS career, Start of End to SSAS and MDX career?) have tried to find reason with this development and at least my understanding is that they are more on the happier side. After this feeling had finally sunk in, I decided to write a pre-mature obituary for one of my favourite tools in my company’s technology page as given below:- “ Is it the end of the road for Microsoft SQL Server Analysis Services? It certainly seems so, Microsoft has dealt a deathblow to the multidimensional approach of OLAP and has announced in today’s PASS summit that it’s focus is on a newer and less multi-dimensional approach – the BI Semantic Model. The BISM – BI Semantic Model – is the name for the new type of Analysis Services database that gets created when you publish a PowerPivot model up to the server. This is clearly a strategy to bring in more ROLAP experts to the tool as the popular perception is that the learning curve for SSAS is pretty steep and many people are put off by it. What it means is that while the MOLAP SSAS won’t be deprecated, the efforts of the SSAS dev team are concentrated on BISM and PowerPivot and we shouldn’t expect any radical new changes for the future. This could mean a gradual death for SSAS by 2012 once Project Denali (which is expected to be renamed as SQL Server 2011, and rumored to be released in the last quarter of 2011) is released and established commercially. So, welcome DAX and bye MDX.” dead-end-sign
Why I termed this as pre-mature is because a new comment has appeared in Chris Webb’s blog from Amir Netz (Lead Architect for Microsoft’s BI offering) and it seems to be promising. Some excerpts from that

  • BISM and VertiPaq are all new features of SSAS. SSAS is gaining massive new capabilities in Denali. Nothing it taken away, nothing is deprecated. It is all net positive. I hope you can not only internalize it but also communicate it to others.
  • The best way I think of the relationship of the “MOLAP UDM” to the “VertiPaq BISM” is akin to the relationship between C++ and C# in the year 2000. While C++ will still stay with us forever, C# is advancing rapidly and is able to take on broader and broader workloads. And the most important thing, Visual Studio – offering both C++ and C# is a much better product then the one offering only C++. It offers developers the option of choosing the right tool for the right task. Now – replace C++ with MOLAP UDM, C# with “VertiPaq BISM”, and Visual Studio with “SSAS” and you got the exact situation of today. 
  • Even with VertiPaq BISM introduced, MOLAP UDM is still used for all the high end heavy lifting and it is just as important as it had always been.
  • As for the roadmap – MOLAP is here to stay. It will have new features every release (just like we have new important MOLAP features in Denali). Yes – BISM being less mature will see a faster innovation pace and being based on a more innovative foundation it will likely be the one creating exciting breakthroughs as we move forward.
  • We worked hard to preserve the investments you made in the UDM and MDX. For example, the BISM supports ODBO and MDX. In fact – this is the only way Excel connects to it. All of the MDX lovers can still send MDX queries and create calculated members in the BISM. This is how Panorama works with the PowerPivot model. AMO works with the BISM as well as with the UDM. etc. Make no mistake about it – MOLAP is still the bread and butter basis of SSAS, now and for a very long time. MDX is mature, functional and will stay with us forever.

Yippee! Again, if I can trust my sense of understanding things, it would mean all is not lost and SSAS is here to stay. I guess I would have to wait for another blog from Chris to completely enlighten common people like me and allay my fears. Till then, I would recommend you guys to go and read Amir’s comments completely in Chris’ blog. And definitely, don’t miss out on the other great blog links that I have given. Update (13/11/2010) Microsoft has come out with an official reply now in the SQL Server Team Blog – Analysis Services – Roadmap for SQL Server “Denali” and Beyond. The blog has tried to put out the fears by underlining that the new BISM model does not replace the traditional UDM model. This is also evident from the model diagram they have posted (given below also) where we can see the relational and multidimensional data models existing side by side. BISM (BI Semantic Model) Some salient points of the blog are:-

  • While PowerPivot is targeted at business users to build Personal and Team BI applications, Analysis Services is the platform for building Professional BI applications.
  • With the introduction of the BI Semantic Model, there are two flavors of Analysis Services – one that runs the UDM (OLAP) model and one that runs the BISM model. This is a side-by-side offering – you can choose to run one instance of Analysis Services hosting a UDM model and another instance hosting a BISM model on the same server.
  • You might ask – Why do we have two types of models in Analysis Services? Which one should I use? Is the UDM going to be deprecated now that we have the BISM? The answer is NO! Analysis Services just got a lot better in SQL Server “Denali” with the BISM and VertiPaq and DAX! However the UDM is a mature and industry leading technology and is here to stay.
  • Some existing UDM applications that are undergoing a major overhaul might be migrated to run on the BISM if appropriate. For new BI applications, the choice between UDM and BISM will depend on the complexity of the application. For BI applications that need the power of the multidimensional model and sophisticated calculation capabilities such as scoped assignments, the UDM is the way to go. For a broad majority of BI applications that don’t need that level of complexity, the BISM will offer a rich, high performance platform with faster time to solution
  • The Analysis Services team is committed to staying on the cutting edge of BI innovation and leading the industry with breakthrough technologies such as VertiPaq. At the same time, we recognize our rich heritage in OLAP and continue to be humbled by success of our product and the vast ecosystem of customers and partners that it has helped us build.

So this blog from Microsoft should answer the question which we asked, the answer – SSAS is here to STAY!

Posted by SQLJason, 1 comment
The Curious Case of Joins while Cube Processing

The Curious Case of Joins while Cube Processing

November 5, 2010

One of those days, I was just chatting with a colleague of mine about some database designs and he was showing me some exquisite piece of work in the database. That is when I happened to glance upon a stored procedure which apparently seemed to materialize an already existing account dimension view as a table. This stroked my inherent sense of curiosity and I unleashed a barrage of questions at him regarding it’s relevance. My colleague started explaining that somehow the dimensions were getting joined with all the fact tables during cube processing and hence the processing takes a very long time to complete if the dimension view was used as such. To add some justice to his words, the dimension view indeed was doing some complex logic and it looked natural that the cube processing would take time if the view was used in the joins instead of a table. But what didn’t look natural to me was that the dimension views were being use din the joins. After all, I was under the assumption that it was just a ‘select from fact table’ that gets executed or at max, the fact views if some logic is used. So I decided to open this curious case of joins for investigation. Even though it wasn’t because I didnt trust my colleague, I just had to see with my own eyes to believe, the doubting Thomas that I am. So I asked him to process a particular fact table and I looked up the query that was being used. With materialized Reference relation Sure enough, the dimension view was being used in the joins. I was dumbfounded and decided to take a break to think. Adding tobacco to my pipe, I put on my thinking cap and stared aimlessly at the setting sun. Random thoughts started racking my brain and I even ventured far to think that it could be a bug in analysis services. Suddenly, something clicked in my mind and I started running towards my colleague and delivered in true Sherlock Holmes style – “Come, Watson, come! The game is afoot. Not a word! Into your clothes and come!”. My colleague also decided to humour me and tagged along. Me : Chance has put in our way a most singular and whimsical problem, and it’s solution is it’s own reward. Colleague : Can you please cut the crap and let me know why this happens? Me : Watson, you know I can’t resist a touch of the dramatic. I have no desire to make mysteries, but it is impossible at the moment of action not to enter into long and complex explanations. Let’s go step by step and have a look at the query once again. Colleague : Duh, ok! Me : Perhaps when a man has special knowledge and special powers like my own, it rather encourages him to seek a complex explanation when a simpler one is at hand. The importance of careful observation can never be emphasized enough. If you would remember the way that you explained the problem to me, you mentioned that all the dimension views were getting joined in the fact table. But if you look at the query (especially the highlighted part), what do you observe? Colleague : Hmmm, just the account dimension is getting joined. Me : How often have I said to you that when you have eliminated the impossible, whatever remains, however improbable, must be the truth? We know now that all the dimensions are not getting joined. We also know that the account dimension is getting joined for some reason. What could be the reason? Colleague : <light dawning on him> It could only mean that we have defined the usage of this dimension differently. Me : Elementary my dear Watson! So let us open the dimension usage tab and check. Dimension Usage Colleague : Yes!!! There is a reference relationship between the Geography dimension and the fact table through Account, that should be the reason! Me : To let the brain work without sufficient material is like racing an engine. It racks itself to pieces. Why should a reference relationship induce a join? After all, it could also be just resolved at run time like the regular relationship. To understand more, click to view the relationship details. materialized option Colleague : Finally! It is because the Materialize option is ticked. Me : Elementary, my dear Watson. When a reference relationship is materialized, the joining across dimensions is performed during processing and not in querying. Also, the attributes in the materialized reference dimensions follow the aggregation rules of standard dimensions. If you remove the tick in the materialize checkbox, you will notice that the joins would not be present anymore. without Materialize Colleague : OK, so this was it. So simple once we know the reason. Me : There, Watson, this infernal case had haunted me for days. I hereby banish it completely from my presence. Colleague : Could you please cut out the Sherlock Holmes part, it’s freaking me out! So, here rests the curious case of joins while cube processing in my chronicles. Note : Special mention for eQuotes for help with the Sherlock Holmes quotes. Also, to read on how the ticking of the Materialize option would affect your data, click on the blog below by Alberto Ferrari – SSAS: Reference materialized dimension might produce incorrect results

Posted by SQLJason, 1 comment
Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)

Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)

September 4, 2010

Around 6 months ago, I was desperately looking for some online resources to help me implement some map reports. The shapefile I was dealing with was at a very granular level (which means that it had a lot of data, 2.5 MB in size), and we needed to do some custom aggregations on the spatial data. As the custom aggregations were done at report run time, it turned out to be a major performance bottleneck. That was when this blog by Sean Boon – SQL Server 2008 R2 Map Tips: How To Import Shapefiles Into SQL Server and Aggregate Spatial Data, caught my eye and I got the idea of importing my shapefile into DB and then doing the custom aggregations there itself for solving my performance problems. Armed with the contents from this blog, I started developing my reporting solution but soon stumbled upon a major roadblock – I realized that the blog he had written was for aggregating shapefiles of Geography data type, while the shapefile I had with me was of Geometry data type (read here to find the difference between the two data types). After a couple of days of searching, I finally managed to find the solution and thought of blogging it down at least now.

Map report

The major steps involved in the solution are:-

1) Converting Shapefiles into SQL Server Spatial Data : As mentioned in Sean’s blog, we will be using the tool from http://www.sharpgis.net/page/Shape2SQL.aspx called Shape2SQL. Download the software from the site and then click on the exe to see the user interface. If you go to the site, you will find an image of the interface which guides you how to convert the shapefile into SQL Server Spatial data but then that is for a shapefile of Geography data type. So how do you convert a shapefile of Geometry data type into spatial data? For that, make sure that the interface looks like the image given below:-

Shape2SQL for geometry shapefile conversion

For some reason, I had to create the table beforehand, the tool didn’t automatically create the table as I thought it would. The table creation script is also given below so that you can have an idea of the columns

CREATE TABLE [dbo].[MapData](
[geom] [geometry] NULL,
[PC4CODE] [nvarchar](max) NULL,
[Sales Rep] [nvarchar](max) NULL)

So as you can see, in my table, I would have all the PC4Code information (which is the postal code) at the end of this exercise. Now I update my table such that I get all the parent information in the [Sales Rep] column (which would have all the list of sales regions). My requirement is that instead of having a map by some 4000 odd postal codes, I would like to see a map by just the 40 odd sales regions that I have. A simple select from the MapData table would look like this now:-

NL Map - Detailed

2) Aggregate Polygons in SQL Server – Get assembly : For aggregating polygons of geography data type, we can use the method that Sean has mentioned in his blog. But that particular function would not work in the case of geometry data type. For doing this, please follow the steps mentioned in ConceptDev (Craig Dunn’s blog) quoted below

DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) or above installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:assemblies.

2. Compile the C# SQLCLR functions in C:assemblies via the Command Prompt

c:windowsmicrosoft.netframeworkv3.5csc /t:library /r:Microsoft.SqlServer.Types.dll C:assembliesaggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM ‘c:assembliesAggregate.dll’
go
CREATE AGGREGATE EnvelopeAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
go
CREATE AGGREGATE UnionAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]
go

sp_configure ‘clr enabled‘, 1;
go
RECONFIGURE;
go

3) Aggregate Polygons in SQL Server – Use function to aggregate data and store in table : Now that you have the aggregate functions registered, you can use it in a query to aggregate it and store it in a table like shown below:-

select WAM=M.[Sales_Rep],
geom=dbo.UnionAggregate(geom.MakeValid())
into NL_Map
from MapData M
group by M.[Sales_Rep]

The UnionAggregate function will aggregate the data and the aggregated data can be viewed by a simple select on the new NL_Map table

NL Map - Aggregated to sales region

Now you are all set to use this table as the source of your reports.

Update (25/03/2012)

SQL 2012 has integrated these features and hence if you are using SQL 2012, you can aggregate both geography and geometry data types using the built in functions Geometry::UnionAggregate(geom) and Geography::UnionAggregate(geog). Refer the blog below http://social.technet.microsoft.com/wiki/contents/articles/4136.aspx

“ UnionAggregate() combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.
SELECT Geography::UnionAggregate(geog) FROM Counties
WHERE name = ‘Washington’;

Posted by SQLJason, 9 comments
Performing a Right Outer Join with a Left Outer Join clause

Performing a Right Outer Join with a Left Outer Join clause

August 24, 2010

Most of the interviewers I have seen tend to prefer people with better problem solving skills rather than better knowledge on a particular technology. No wonder considering the fact that technologies change with each fleeting moment, while a good problem solving mentality stays forever. With this prelude, I put forward this question that I came to hear from one of friends recently – How do you perform a right outer join with a left outer join clause? And no, you can’t swap the tables here. Disclaimer : This question is purely to test your knowledge of how the said Joins work and to test your problem solving skills. It may not serve any real purpose in a practical project scenario. Question Lets say, I have the following tables Demo and Demo2 Query : select * from Demo Output Query Result - Demo Query : select * from Demo2 Output Query Result - Demo2 Now using a left outer join, the result should be equivalent to the output of SELECT A.attribute,
       B.attribute
FROM   Demo2 AS A
       RIGHT OUTER JOIN
       Demo AS B
       ON A.attribute = B.attribute; Output Demo2 Right outer join Demo
And you can’t swap the order of the tables i.e. Demo2 should be on the left hand side and Demo should be on the right side. Answer Now this exercise is basically to inculcate the importance of problem solving and hence I will try to detail out the steps as much as possible. Lets go step by step:- 1) The thing to understand here is that we can’t use the left outer join with the normal join condition like A.attribute = B.attribute, the reason being that we would get all the rows of the left side table (Demo2). Eg: SELECT A.attribute,
       B.attribute
FROM   Demo2 AS A
       LEFT OUTER JOIN
       Demo AS B
       ON A.attribute = B.attribute; Output Demo2 left outer join Demo So somehow we need to bring the required rows in. 2) Now that we have understood that the above join condition won’t work, we need to think of another join condition. Let’s say, what would happen if we give a condition that is always true on the ON clause, something like 1=1? SELECT A.attribute,
       B.attribute
FROM   Demo2 AS A
       LEFT OUTER JOIN
       Demo AS B
       ON 1=1; Output join on 1=1 Cool, now we have got a cross product. 3) Now the thing to do is to understand what a right outer join is really. If the column on the right hand side matches with the column on the left hand side based on the join condition, then both the columns are displayed. Else, a NULL will be displayed on the left hand side with the correct column on the right hand side. For this first we can write a subquery in the columns like shown below SELECT (SELECT attribute
        FROM   Demo2 AS c
        WHERE  c.attribute = b.attribute) AS attr,
       B.attribute
FROM   Demo2 AS A
       LEFT OUTER JOIN
       Demo AS B
       ON 1 = 1; Output Subquery result 4) Now all we need to do is to take the distinct and then we get the output as required. SELECT DISTINCT (SELECT attribute
                 FROM   Demo2 AS c
                 WHERE  c.attribute = b.attribute) AS attr,
                B.attribute
FROM   Demo2 AS A
       LEFT OUTER JOIN
       Demo AS B
       ON 1 = 1; Output Demo2 Right outer join Demo So, all is well? Not really. This particular query would break when we get duplicates. Suppose the TH row was duplicated in Demo table. Now, when we take the distinct after applying the subquery on the cross product, we will still get only one row for TH when a right outer join would have given 2 rows. So I came up with the below query to solve this particular scenario:- SELECT attr,
       attribute
FROM   (SELECT row_number() OVER (PARTITION BY CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END, b.attribute ORDER BY a.attribute) AS rnk,
               CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END AS attr,
               B.attribute,
               row_number() OVER (PARTITION BY b.attribute ORDER BY CASE WHEN A.attribute = b.attribute THEN A.attribute ELSE NULL END DESC) AS rnk2
        FROM   Demo2 AS A
               LEFT OUTER JOIN
               Demo AS B
               ON 1 = 1) AS Outr
WHERE  (Outr.rnk = 1
        AND Outr.rnk2 = 1)
       OR (attr = attribute); Please post your queries in the comments if you have got alternate solutions, I would be pleased to have a look at them.

Posted by SQLJason, 0 comments
London Ahoy!

London Ahoy!

July 27, 2010

To all those folks who have been wondering where I disappeared – I just relocated to London from Bangalore this Sunday, 25 July! I was extremely busy in the preparations and hence couldn’t blog much and by the looks of it, the next 2 weeks also looks pretty hectic. I have to find an accommodation somewhere in London within the next 12 days, my office is in 288 Bishopsgate (near the Liverpool Street Station). So if any of you guys know a decent place for a single guy, please do pitch in, I would appreciate it 🙂 Hopefully, I would be meeting a lot of you who are in London. Till then, adieus amigos. london

Posted by SQLJason, 1 comment
Aggregate of  an aggregate function in SSRS

Aggregate of an aggregate function in SSRS

July 3, 2010

I am worse than a child when it comes to colours and no wonder, spend a lot of time on the aesthetics whenever I am preparing an Excel sheet or a PowerPoint presentation. For me, this is rather a way to indulge the child within me than to make it more presentable to the audience. The joy that you get when you arrive on that perfect colour for your headers or that perfect font to suit the mood or that perfect design template for your slides is something that can’t be expressed. Recently, I was preparing the sample data for a report and playing around with the colours in excel as usual when I noticed that there was an unexpected aggregation that would be required. Beads of sweat broke from my forehead as I contemplated going back to the user and saying that I might need some time to think whether this is possible. Let me give you an overview of the requirement. The measure (Order Count) comes in at a sub category level and the client wanted to see the average of the subcategories within a category, as well as an average of average as the grand total (refer below image for calculations) Aggregate CustomCode  I straightaway opened BIDS and decided to try it out for myself. I quickly made the dataset and designed the layout as shown below. Design mode - layout On previewing the report, I got the following output Preview I was almost giving that smug grin of mine thinking everything is fine, when I spotted that the Grand Total doesn’t match with the requirements. The totals were coming properly at the category level (which were just an average of the sub categories) but at the Grand Total level, instead of taking an average of the Categories [ (7585 + 7857 + 4082) / 3 ], it was still taking an average of the sub-categories [ (5212 + 7512 + 10030 + 6185 + 9528 + 3382 + 4781) / 7 ]. That is when I realized that what we need here is an average of an average at the grand total level. But SSRS 2008 and below versions doesn’t allow us to define an aggregate of an aggregate function. This was confirmed when I got the following error on replacing Avg(Fields!Order_Count.Value) by Avg(Avg(Fields!Order_Count.Value)) [rsAggregateofAggregate] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions. My next approach was to try using the Avg function on a textbox which would already have the value Avg(Fields!Order_Count.Value), something like Avg (ReportItems!Textbox21.Value). The result was again an error, not much different from the previous error [rsAggregateReportItemInBody] The Value expression for the textrun ‘Order_Count9.Paragraphs[0].TextRuns[0]’ uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers. That is when I decided to dig into the treasure troves of the MSDN forum, and unearthed this post by Jin Chen (MSDN Moderator) where he gives a sample custom code to achieve the functionality. Armed with a minorly tweaked version of this custom code, I followed the steps below to achieve my requirement:- 1) Go to design mode and right click on the area outside the report body. Select Report Properties Report Properties 2) Select the Code tab and paste the following code there Dim public totalBalance as Integer
Dim public Cnt as Integer
Public Function AddTotal(ByVal balance AS Integer ) AS Integer
                totalBalance = totalBalance + balance
Cnt=Cnt+1
                return balance
 
End Function
 
Public Function GetTotal()
                return totalBalance/Cnt
End Function
  The AddTotal function basically adds all the values of a textbox and sets it to totalBalance variable. This function also counts the number of values. The GetTotal function returns the sum of the values by the count of the values which is basically the average of the textbox values. Once you have pasted the code, you can click OK. Custom code window 3) Replace the expression at the Total level from =Avg(Fields!Order_Count.Value) to =Code.AddTotal(Avg(Fields!Order_Count.Value)) This is done so that when the average is being calculated at this textbox during report rendering, at the same time the sum and count of these averages will also be calculated. 4) Replace the expression at the Grand Total level from =Avg(Fields!Order_Count.Value) to =Code.GetTotal() This function will return the computed value, which would be the average of the average as we required. 5) Preview the report to confirm that the output matches with the requirement. Preview result Guess who is grinning smugly now? 😀

Posted by SQLJason, 13 comments
SSAS Dimension Attribute Properties : Part 2

SSAS Dimension Attribute Properties : Part 2

June 23, 2010

Yesterday, I was reading through some articles and happened to glance through a very memorable quote by  Berthold Auerbach – “ The little dissatisfaction which every artist feels at the completion of a work forms the germ of a new work “. That was when I realized I haven’t yet completed my dimension attribute article and so here I am with the second part. In SSAS Dimension Attribute Properties : Part 1, we reviewed the Advanced and Basic properties of dimension attributes. This article would be continuing with the Misc, Parent-Child and Source properties.

Dimension Attribute Property

The properties are explained below:-

MISC

1) AttributeHierarchyOrdered : This particular property specifies whether the members of the attribute are ordered or not. The values of this property can just be True or false. If the order of the members do not matter, setting this property to false for attributes where the attribute hierarchy is enabled or high cardinality attributes can significantly increase the processing performance.

2) GroupingBehavior :  This property is used to give a hint to the client application whether to encourage or discourage users to group on this attribute and does not affect any of the structures on disk. The values are EncurageGrouping and DiscourageGrouping.

3) InstanceSelection : This property also is used to give a hint to the client application’s UI on the recommended means of selection of a member from the attribute. The options available are

  • None – (Default) no selection used.
  • DropDown – Appropriate for situations where the number of items is small enough to display within a dropdown list.
  • List – Appropriate for situations where the number of items is too large for a dropdown list, but not large enough to require filtering.
  • Filtered List – Most useful in scenarios where the number of items is large enough to require users to filter the items to be displayed.
  • Mandatory Filter – Appropriate for situations where the number of items is so large that the display must always be filtered.

4) MemberNamesUnique : This property indicates whether the member names are unique across the attribute hierarchy and this property affects the way member unique names are generated. The available options are True or False.

Parent-Child

1) MembersWithData : In a parent-child hierarchy, some of the non-leaf members may also have data associated with them (unlike normal hierarchies, where the non-leaf members have a value equal to the sum of it’s leaf values). These members are called data members and are present only for parent-child hierarchies. This particular property is used to set the visibility of the data members in parent-child hierarchies and the available options are NonLeafDataHidden and NonLeafDataVisible. This MSDN article – Working with Attributes in Parent-Child Hierarchies does a very nice job of explaining this property with an example.

2) MembersWithDataCaption : This particular property is used as a naming template for the system generated data members. For eg, if we have the MembersWithData property set to NonLeafDataVisible, then a leaf member representation of the data member is added. For eg, if Jason is a data member, with Thomas and Tom as his leaf members, then there would be an additional Jason added as a leaf member. Now to differentiate between the leaf member and the data member (in this case, both are Jason) would be difficult and hence we can use a template like *(leaf member) as the value of this property. The asterisk symbol is a placeholder for the original name. So our example would become Jason for the data member and Jason(leaf member) for the leaf member.

3) NamingTemplate : This property specifies how levels in a particular parent-child hierarchy would be named. Click the ellipsis button (..) in this property’s cell and then you should be able to view a popup window as shown below:-

Level Naming Template

You can specify a name for the level by clicking on the Name column of the second row and entering for eg, Employee *. This will ensure that instead of Level 02, Level 03, etc., you will be getting Employee 02, employee 03 and so on. For more details, refer to the MSDN article – Defining Parent Attribute Properties in a Parent-Child Hierarchy.

4) RootMemberIf : This property is used to specify the criteria by which we can identify the members of the highest level (excluding the ALL level). Again quoting from an article of William Pearson – “ The four selection options include the following:

  • ParentIsBlankSelfOrMissing – (Default) Only members that meet one or more of the conditions described for ParentIsBlank, ParentIsSelf, or ParentIsMissing are treated as root members.
  • ParentIsBlank – Only members with a null, a zero, or an empty string in the key column or columns are treated as root members.
  • ParentIsSelf – Only members with themselves as parents are treated as root members.
  • ParentIsMissing – Only members with parents that cannot be found are treated as root members.

The behavior of the RootMemberIf property in determining how the root or topmost members of a parent-child hierarchy are identified, is, therefore, dependent upon which of the selections above is made. The default, as noted above, is ParentIsBlankSelfOrMissing.

5) UnaryOperatorColumn : We can control how members on a hierarchy (usually parent/child, but not necessarily) aggregate up to their parents by defining unary operators. To do this, you must create a new column in your dimension table to hold the unary operator values and then set the attribute’s UnaryOperatorColumn property to point to it. This property specifies the column which holds the unary operator. You will find a very good example under the Unary Operators and Weights heading of this article – Measures and Measure Groups in Microsoft Analysis Services: Part 1. The values are (none) and (new) for this property. On clicking New, a dialog box opens which will prompt us to select the binding type, source table and the source column.

Source

1) CustomRollupColumn : Unary operators do not give enough flexibility for rollup, and in such cases, we can write our own rollup formulas as MDX expressions. This property is used to specify a column which will contain the custom rollup formula. A valid expression will ensure that the aggregation logic defined in the AggregateFunction property of the measure would be overridden for this attribute.

2) CustomRollupPropertiesColumn : This property is used to contain the properties of a custom rollup column. Refer Custom Member Formulas heading of this article – Measures and Measure Groups in Microsoft Analysis Services: Part 1 to learn more about the above two properties.

3) KeyColumns : This property contains the column/columns that constitute the key for the attribute, which is the column in the underlying relational table in the data source view to which the attribute is bound. If the NameColumn property is not defined, the value of KeyColumns property would be used to display the attribute members.

4) NameColumn : In most of the cases, the key of the attribute would be a integer value, and this would not make any sense to the user who is viewing the attribute members. For this, we can specify a column in this property which will have the user friendly name of the attribute member.

5) ValueColumn : This property identifies the column that provides the value of the attribute. If the NameColumn element of the attribute is specified, the same DataItem values are used as default values for the ValueColumn element. If the NameColumn element of the attribute is not specified and the KeyColumns collection of the attribute contains a single KeyColumn element representing a key column with a string data type, the same DataItem values are used as default values for the ValueColumn element. In KeyColumns and NameColumn property, there are additional properties which can be got on expanding the plus symbol on the left. This article gives a pretty good overview on them. With this info, you should be all set and ready to give that killer answer in your next interview. Godspeed and good luck to you! 🙂

Posted by SQLJason, 6 comments