Month: October 2011

Tracking Data over Time Visually in Power View aka Project Crescent

Tracking Data over Time Visually in Power View aka Project Crescent

October 18, 2011

All right, I know most of you Microsoft BI fans would be gushing over the new announcements made in PASS Summit 2011 last week. Now onwards, Project Crescent will be officially known as Power View, SQL Server Denali as SQL Server 2012, Juneau as SQL Server Data Tools and finally, we have a mobile BI strategy! (you can read the official press release from here and some more details on Power View from here). I decided to celebrate the occasion by blogging about one of the new features I particularly like in Power View in CTP3 version – tracking data over time visually. For this demonstration, I have used the computer in SQL Server Virtual Labs. This is a great place to kick-start your SQL Server 2012 journey. Apart from the great tutorials, I also access it when I have to test out some new feature. Follow the steps below to create a Power View report with the ‘track data over time’ functionality:-

1) Go to the SharePoint site and click on Data Sources. Then click on the small dropdown icon on the right of the data source name and select the Create ‘Crescent’ Report option as shown in the image below.

1 Create Crescent Report

2) If this is the first time you are opening the Crescent report, there is a chance that you might get the following error –
An error occurred while loading the model for the item or data source. Verify that the connection information is correct and that you have permissions to access the data source.

2 First time error when opening Project Crescent report

This is a bug within the current CTP and this should go off if you refresh the address bar of the browser.

3 Crescent report layout

3) Select Category under Product and Quantity under Sales. A table will be displayed in the layout with the values of Category and Quantity.

4 Selecting Category and Quantity

4) Click on the Scatter chart option as shown in the image below.

5 Selecting scatter chart

5) Resize the chart to occupy the full space. A scatter chart requires 3 measures – for the X axis, Y axis and the size of the bubble.  Since we have already selected the Quantity measure, we just need to select 2 more measures for the Y axis and size. Select Revenue and # Products.

6 Selecting measures for scatter chart

6) Once that is done, select Year under Date and this will automatically be taken as the Play Axis of the scatter chart. You should be able to get the graph as shown below.

7 Select Year as the Play Axis

7) Give a title and then click on the save button on top left corner. Enter the destination path as well as the file name and then click ok to publish the report in SharePoint.

8 Publish reports

8) Go to the destination path specified in the step above and click on the report name to preview it. Click on the play button to see visually how the Category changes over the year.

Track Categories over Years

You can also click on a particular category and track the changes for that particular category over the year.

Track Selected Category

Isn’t that sweet and simple? Smile Coming from a manufacturing domain, I can already think of lots of scenarios where this can be used (e.g.: tracking price changes, profitability, revenue, stock, sales, etc over time). Of late, I have come to realize that some people face difficulties in following blogs by just reading the steps or by seeing a few images. So, I have taken a conscious decision to video-blog the steps whenever I can. You can find the video for this blog below

Tracking Data over Time in Power View

Let me know your feedback about this initiative and whether it is helping in conveying my message better. Cheers!

Posted by SQLJason, 2 comments
Generating QR codes in SSRS

Generating QR codes in SSRS

October 10, 2011

Over the short span of my career, I have seen many people get burnt out and change their careers from technology to some other field. It is easy to get disillusioned with a job that requires you to sit on a chair the entire day and “code” the same stuff. At the same time, I have also met people who are so passionate and enthusiastic about what they do and are still coding away to glory after 15-20 years. What makes those people different, and why don’t they get bored of the “same, old mundane coding stuff”? The answer is simple if you do get to observe them – their creative spirit. To quote Edward De Bono, “Creativity involves breaking out of established patterns in order to look at things in a different way.” For these people, every day teaches them something new and they like to challenge their limits. They make out new things from the same stuff by looking at it from a different angle. I always make it a point to imbibe this quality in the people I mentor, and try to put across some puzzles where they have to reuse their previous learning in a new way. One of my recent questions was on how to display QR codes in SSRS. Generating QR code in SSRS
On asking this question, most of the answers involved in getting some custom code written (but no-one knew how to write the code) or to get some plug-in from third party vendors (which involved licence costs). But the team had to put on their thinking caps when I said that the only thing they would require is access to the internet. For demonstrating the purpose, I have made a report which will generate business cards for all the employees in my fictional company with QR codes. Follow the steps below to generate the report:- 1) Create a dataset with the following query SELECT        ‘Jason Thomas’ AS name, ‘’ AS URL, ‘00447574713732’ AS mob
SELECT        ‘Jay Thomas’ AS name, ‘’ AS URL, ‘67891’ AS mob
SELECT        ‘Jean Elizabeth’ AS name, ‘’ AS URL, ‘12345’ AS mob Instead of this, you can also use information from your database tables or SSAS cubes. For the sake of simplicity, I am directly hardcoding my dataset. 2) Drag and drop a list into the layout. Then insert the name, URL and mob details into the list. Also add a company logo to make it look jazzy Winking smile BIDS Layout - List 3) Drag and drop an image to the list and select the image properties. Image properties 4) Go to the General tab and select the Image Source as External. Then click on the expression and enter the following expression =”” + Fields!name.Value + Constants.vbcrlf + Fields!URL.Value + Constants.vbcrlf + Fields!mob.Value This is shown in the image below. How to generate QR code (You can append any string after the URL though I am using the Name, URL and mobile here) 5) Click on preview and you should be able to get the desired results. End Result - QR codes in SSRS The secret of getting this done is by using the Kaywa site which will accept parameters and output the QR code for that as an image. Now there are plenty of sites online which will generate QR codes as well as barcodes in this format, and you can use any one of them for doing the same. And since you have already learned before that it is possible to display External images in SSRS, generating QR codes or barcodes is just an application of that knowledge. Disclaimer
This report was developed just for having some fun with SSRS and for educational purposes. For people who are going to be using this for commercial purposes, I would ask them to go through the conditions of the site which generates QR code (for eg, Kaywa states it’s use should be only for non-commercial reasons).

Posted by SQLJason, 5 comments
uCertify 70-448 PrepKit Review

uCertify 70-448 PrepKit Review

October 8, 2011

Ok, let me put it up very bluntly – I am not a big fan of all those test and preparation sites for certifications that keeps floating around. The main reason for my dislike is that the questions are usually the braindumps of the exams which have been leaked out and many people can cheat their way to a certification just by studying the dumps (I still don’t get it why the questions are never changed or shuffled on a regular basis for the Microsoft certifications). This eventually affects the reputation of the certification as well as the credibility of the people who have been certified. And for people who have done it the hard way, it could mean that their efforts have gone in vain. This was the first thought that came to my mind when uCertify asked me to review their test preparation software for 70-448 recently and I was certain that the product would face the ire of my blog if this was going to be another dumps based test site. Luckily it didn’t. 0 uCertify HomePage The very first thing that came to my notice after installing the software was the elegant and user friendly UI. 1 uCertify UI The UI has got separate sections for tests, study materials and progress tracking. The tests can be taken in two modes – learn mode (where you can review your answers immediately and read the notes also for it) and test mode (where you would be able to see your answers and score only at the end of the test). uCertify has neatly classified the tests so that there is a diagnostic test in the beginning to assess your level, practice tests to improve your skill levels and the final test (which I think is a bit harder than the real one). The study material section is also pretty handy as it contains all the key concepts in one place. 2 StudyMaterial The test readiness section tracks your history and gives you an indication of how ready you are to take the certification. 3 TestReadiness Another great feature is the Study Helper which explains the key concepts in each of the six chapters required for MCTS 70-448. 4 Study Helper Now, coming to the pros and cons of the 70-448 PrepKit Pros
1) Extremely strong and user friendly UI which I believe is shared for all the uCertify PrepKits.
2) uCertify’s strong policy against Braindumps
3) Comprehensive set of questions with study notes (300+) which are neatly classified
4) Test readiness feature gives an indication of when you should appear for your exams
5) Money back policy if you don’t pass within your first attempt
6) Good simulation of the test environment. Cons
1) Even though the overall quality of the questions are good, there are some areas of improvement. For eg, questions with pictures of the actual development environment are less.
2) The How To section (visuals explaining how to do something) and Articles section are pretty much empty for 70-448. Overall
My experience suggests that the 70-448 exam is a piece of cake for people with at least 2 years of experience in SSIS, SSAS and SSRS and they would pass the exam by buying and studying the official book – MCTS Self-Paced Training Kit (Exam 70-448). But for people who would want to get a feel of the tests before the actual exam or for those who don’t have an all-round work experience in the three sections (SSIS, SSAS, SSRS), I would recommend uCertify 70-448 PrepKit. It’s a good investment considering that you will get your money back if you don’t pass within your first attempt. And also, you can be proud of the fact that you tried to pass the exam honestly and not by depending on the illegal dumps. P.S.
I am not being paid by uCertify to write this review nor am I getting any benefits out of the sales made through this review. However, my readers can avail a discount of 10% (in addition to any existing sale) on any test preparation software from uCertify by putting the discount code ‘UCPREP’ in the shopping cart.

Posted by SQLJason, 1 comment
SQLBits 9 – Hangover

SQLBits 9 – Hangover

October 6, 2011

At the time of writing this post, I can see yellow leaves falling to the pavements and feel the cold breeze kissing my cheeks. No matter how hard I try to resist it, reality is dawning on me that the summer has gone by and that the winter is finally settling in (yeah, I know some of you might argue that we never had a summer. After all, it’s been the coldest summer in 18 years). Can’t believe that it was only a week before many of us were sweating in the big halls of the Adelphi Hotel in Liverpool. Oh sheesh, I did it again! I tried to avoid it but just like the winter, here comes the familiar feeling of hangover. SQLBitsLogo
This was my third SQLBits and like always, I had been counting down the days from the time SQLBits was announced. Well, this time I had an additional reason – I was presenting a session at SQLBits. More than that, I had also got a free entry on Friday because I was a speaker (now that calls for a big HURRAY!). Even a loss of pay on Friday couldn’t deter me from attending the conference on Friday (I had already exhausted my annual leaves for my wedding in JulySad smile) and I reached there right in time for the keynote. Even though the agenda suggested that I would be seeing cloned versions of Steve Tramack in both the Aintree and Lancaster halls using the latest Microsoft & HP technologies (check the agenda here), all I got to see was a bigger room with the wall removed and one Steve Tramack. Or wait, I thought I saw two people on the stage and it definitely wasn’t two Steves. Being a victim of the hardships involved in installing and configuring SharePoint myself, I totally appreciated the idea and thought behind the HP Fast Track Solutions for Microsoft SQL Server. The best part is that you can configure a system within a couple of hours what would have definitely taken some weeks. Overall, it was a brilliant start to SQLBits. SQLBits Keynote After that, I went to Matt Masson’s (twitter | blog) session on ‘What’s New in SQL Server Denali for SSIS’. The Facebook social life manager demo was really cool and unique, and also had a peek at many of the new features of SSIS. Matt Mason on SSIS DenaliMatt Mason on SSIS Denali Next on my list was ‘Basic BISM’ by Chris Webb (blog). This was an introductory session on Business Intelligence Semantic Model and was extremely helpful to people like me who were more or less on the start of the learning curve. Chris also touched upon the advantages and disadvantages of the tabular mode in this session. Chris Webb on BISMChris Webb on BISM Then I went for the ‘Myths and Monster of Flash’ session by Fusion-IO where Steve Wharton (twitter | blog) presented some of the characteristics and mechanics of the NAND flash. Steve Wharton - Fusion io Later I spent some time walking around the stalls and talking with the exhibitors. It was so interesting and informative at the same time that I actually spent a lot more time than I intended to and missed the next session as a result. SQLBits exhibitor space The next session was one of my most awaited ones – ‘iPad and Powerpivot-Mobile Business Intelligence in action’ by Jennifer Stirrup (twitter | blog). I had been planning to catch hold of Jen’s sessions for a long time and finally made it this time. Not to say, I had a great time learning about mobile BI and some of the best practices in visualizations. IMG_0103IMG_0104 One of the other things I was really looking forward to was meeting Hrvoje (twitter | blog) and Tomislav Piasevoli (twitter | blog) who were coming down to SQLBIts all the way from Croatia. So after the sessions, I met up with the Piasevoli brothers and we headed to the Cavern Pub for washing down our throats. Had an extremely great time with them as is evident from all the photographs. Tomislav, me and Hrvoje at Cavern Pub We were keeping a tab on the time as we did not want to be late for the much anticipated event of the night – James Bond Casino Night. It really was a great experience walking in to the hall which had now transformed into a casino with games modeled after the ones seen on and we started gambling with the fake money provided to us. Most of the people were appropriately dressed in their James Bond attires also, which made the experience even better. I am sure all of us would have left the place declaring this as the best post event party in SQLBits till date. SQLBits James Bond Casino Night The next day, I had to wake up at an unearthly hour as I had to rush for my 8.10 am session on ‘Fast Track to Spatial Reporting’. The demo material for my session are available for download here. Got a few pics of my session too, thanks to Tomislav. Jason on Spatial ReportingJason on Spatial Reporting The next three sessions I attended was definitely the highlight for many in this version of SQLBits. The first of the series was ‘Building Great Models for Crescent’ by Kasper Jonge (twitter | blog). His session was jam packed with demos and being a reporting person myself, it was really nice to watch all the new features in Crescent. Kasper Jonge on CrescentKasper Jonge on Crescent The next of the series was ‘Vertipaq vs. OLAP: Change your Data Modelling Approach’ by Marco Russo (twitter | blog). Most of the perceptions that I had built about data-modelling was challenged and I was forced to see them in a new light when I am modelling in Vertipaq. DSC_0138IMG_0111 Alberto Ferrari (twitter | blog) was next on the stage with ‘Many-to-Many Relationships in DAX’ and I was seriously amazed at how he presented such a complex topic in such an easily understandable format. This was one of the moments where I wished if only I had such people as my professors when I was studying in my University. DSC_0144IMG_0115 I checked in to the ‘Lightning Talks’ soon after, something which I wanted to do in last time’s SQLBits itself but couldn’t because it was clashing with another session. It was great to see the likes of Kasper Jonge, Martin Newman, Andrew Fryer, Richard Douglas, Ed Vassie, Neil Hambly and Tobiasz Koprowski share the same stage. IMG_0117IMG_0118 The last session of the day was kept aside for Duncan Sutcliffe’s (twitter | blog) session on ‘Kerberos-All You Need to Know in One Hour’. As usual, Duncan just blew everyone apart with his unique presentation style and all in all, it was one of the most comprehensive sessions on Kerberos that I have seen. IMG_0119 Soon we had the customary post event party and lots of prizes were announced from Kindles to Xboxes. A big thanks to all the sponsors without whom SQLBits would not have been possible. Sponsors Also a moment thanking all the guys behind this – Simon Sabin, Christian Bolton, James Rowland-Jones, Martin Bell, Darren Green, Chris Webb, Allan Mitchell, Tim Kent, Chris Testa-O’Neill and all the helpers. A big, BIG, BIG thanks to all of you guys. Once the party was over, all that was left for us to do was to reminisce the last two three days and wait eagerly for the next version of SQLBits. Well, myself, Hrvoje, Tomislav and Kasper also found something else to do while reminiscing Winking smile, see below for a hint. Jason, Kasper, Hrvoje and Tomislav As for the winter, I would like to borrow a few lines from Percy Shelley – If Winter comes, can Spring be far behind?

Posted by SQLJason, 3 comments
Fast Track to Spatial Reporting – SQLBits session material for download

Fast Track to Spatial Reporting – SQLBits session material for download

October 2, 2011

So one more objective struck off from my annual list. Even though I had nightmares where my laptop crashed just before my session, nothing untoward happened and I managed to complete my first session at SQLBits without any problems. SQLBits 9 Query Across the Merseyside Speaker The session was scheduled on Oct 1 at 8.10 am in the morning (at a time when I would normally be snoring in my bed). There was a brief moment of confusion when I discovered around 7.30 am that my laptop did not have the VGA port for plugging in the projector (the new DELL systems just have the HDMI and mini USB ports). Luckily, James Boother (twitter | blog) and Neil Hambly (twitter | blog) helped me get a converter and salvaged the situation. I was able to complete all the demos within the stipulated 1 hour time though I thought I had to race at some parts. Anyways, I will be blogging about my overall SQLBits 9 experience in detail soon. Till then, feel free to download the materials that I used in my session below:-

  • Session slides – contains the PowerPoint presentation (2 MB)
  • Report RDLs – contains all the report files used in the demo (12 MB)
  • Database backup – contains the spatial database used for the reports (41 MB)
  • Demo notes – short notes describing the demo (5 KB)


Posted by SQLJason, 5 comments