Book / Tool / Software Reviews

Book Review : Visualizing Data with Microsoft Power View

Book Review : Visualizing Data with Microsoft Power View

June 19, 2012

“A truly good book teaches me better than to read it. I must soon lay it down, and commence living on its hint. What I began by reading, I must finish by acting” – I still remember the first time I read this quote from an article about Henry David Thoreau. Even though it was a long time back, I can’t forget the ample amount of time I spent pondering over the depth of these seemingly simple words. It is arguable whether the meaning of these great words ever dawned upon the 14 year old that was me at that time, but what I can say with conviction is that this new book that I am reviewing made me remember and reflect about it once again.
Buy "Visualizing Data with Microsoft Power View" SQL Server 2012 has been an exciting release for techno geeks like me, and Power View has been one of the star attractions. Being a reporting enthusiast, I spared no pains in getting my laptop upgraded so that I could get to the bare minimums for testing out Power View. From the little time I spent with Power View, I found it to be really simple and easy to understand. Hence when I heard that there was a new book on Power View coming to the stands, my first question was – How on earth could there be enough matter to fill a whole book on Power View? I was pleasantly surprised when I received my copy of “Visualizing Data with Microsoft Power View”. The authors (Brian Larson, Mark Davis, Dan English and Paul Purington) have done a splendid job of explaining all the features of Power View in an easily understandable format. Even though the target audience for this book are Power View beginners which includes non-technical business users, it also gives professionals who are acquainted with the tool (like me) an opportunity to review all the functionalities and fix the gaps in learning. Let me summarize the contents in a nutshell -the initial chapter takes the reader through an introduction of Power View and chapters 2, 3 and 4 concentrate on the different visualizations available in Power View. Chapter 5 builds up on the previous chapters and discusses the interactive features of Power View (this is the chapter where my favourite feature of Power View is being explained – the Play axis). Chapter 6 gives a fitting end to the Power View part by explaining how to save, secure, print and export Power View reports. Apart from the above 6 chapters, there are another 5 chapters on how to create a BI Semantic Model (BISM). This is important as Power View requires this layer called  BISM between the report and the data. As the author rightly says in the video, this layer might already be built for the users by the IT team. But there is nothing to fear even if this is not the case as the chapters give a good introduction on BISM. This is not intended for the readers who are trying to get advanced knowledge in BISM and there are other books in the market for that. But if your main intention is to create Power View reports and you would like to learn the basics of BISM for the said purpose, there is no better book than this at the moment. The book also has an accompanying DVD with more than 4 hours of video demonstrations for people who like to learn visually. The appendix section gives instructions on how to setup a virtual learning environment as well as configuring the sample data. With all that said, I can say this book is a must have for anyone interested in using Power View reports or looking to expand their knowledge on Power View. Go ahead and buy the paperback edition from this link or get it on your Kindle from this link (it is at a very affordable price too). Meanwhile, I will be having fun going through the exercises. As I said in the beginning – What I began by reading, I must finish by acting.

Posted by SQLJason, 0 comments
Review: OfficeWriter v8

Review: OfficeWriter v8

February 21, 2012

As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using excel and word. reporting-services-diagram So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up. You can quickly have a look at one of the reports created with OfficeWriter by clicking on the image below. OW Sample Report Let me note down quickly what I thought of it:- Advantages
1) XLSX support – Right now, xlsx support is available only for SQL 2012, but with OfficeWriter, you can take advantage of all the features including overcoming the 65,326 row limit (listed here) in your current version of SSRS.
2) Full excel features support including charts and pivot tables – Since excel is fully supported, you can make use of all the charts, macros and other features which are there in Excel but not in SSRS.
3) Ability to have 2 views for a report –  If we are editing a report with OfficeWriter from an existing report made through BIDS, then we will have 2 views for it – one which is displayed in report manager and other would be when exported to Excel. It can be helpful in scenarios where you view a fixed report and want to drill down and play around with the data further, in which case you can export to excel and then use a pivot table.
4) Charts are not rendered as images when exported to excel. The export to Excel feature in SSRS will return images rendered as a static image. However, with OfficeWriter, you get the chart exactly as you designed in Excel with all the the interactivity and even tooltips.
5) Can enable subscriptions, security – The tight integration with SSRS ensures that we can use subscriptions and other security features available in SSRS for the OfficeWriter reports.
6) Workbook Protection – You can password protect your workbook or lock your cells for edit, which is an often requested feature by SSRS power users for subscription. Limitations
1) Can’t build MDX queries in OfficeWriter, will have to make in BIDS and then use them.
2) Will not work on the 64 bit version of Office, as of now.
3) This is not a limitation actually and is by design, but I couldn’t help wishing if only the charts and data designed through OfficeWriter in excel would display properly in SSRS also. Even though OfficeWriter has more features including integration with Word and SharePoint, I have reviewed only the integration with SSRS for Excel part. For folks who are further interested in this, I will show you how to create a sample report using OfficeWriter and post some useful links below it. Creating / Editing  a sample report For the demo purpose, I am using OfficeWriter v8.0, the 32 bit version of Excel 2010 and SQL Server 2008 R2. Once the installation of OfficeWriter is done, you can find a new toolbar like shown below in the Add-Ins tab of Excel. 1 OfficeWriter Toolbar Now, there are two ways in which you can proceed from here –
I) Create a new report in Excel with OfficeWriter
II) Edit an existing report created with BIDS (Business Intelligence Development Studio) and deployed to the server The steps to create a new report is shown below:- 1) Click on Add Query in the OfficeWriter toolbar and enter a name for your query. 2 Add Query 2) Add a new Database connection if there is none existing, enter the server information and add the required tables for the SQL query. Once that is done, select the required columns. In this example, I have connected to the AdventureWorks database and used the English Product Name in DimProduct table & Order quantity measure in the FactInternetSales. 3 Add DB and query  3) Even though you can group using the OfficeWriter features, I edited the SQL query directly to group by the English Product Name, as shown below and click on OK. Close the query editor after that. 4 Modifying SQL Query  4) Click on Insert Field and select the English Product Name in cell A1 and Order Quantity in B1. 5 Insert fields to excel sheet 5) Click on View. This will prompt you to enter the name of your report and save it in your local drive. Click on Save and a new dialog box opens which will prompt you to enter the name of your report server. Click on OK to publish your report to the report server. 6 Save the report 6) Once that is done, the report preview would be shown in Excel. You can click on Close Report View to go back to the designer. 7 Preview report 7) You can also preview the report from the report manager, which will display a message that the report was made in OfficeWriter and would display correctly only when exported to Excel with OfficeWriter. 8 Preview report in IE Editing an existing report is also simple but you would not be able to edit the queries made through BIDS. Follow the steps below to edit an existing report. 1) Click on Open Report and then click on Retrieve as shown in the image below. 9 Open existing report 2) Enter the report server url and click on Refresh. The list of existing reports would be displayed below and you can select from them. 10 Select existing report 3) Insert the fields after selecting the datasets (notice that Add Query and Edit Query is disabled). You can also include charts as shown below. 11 Insert fields and chart 4) Click on View to save and publish your reports. You can then preview the report in excel. 12 Report Preview
Useful Links
1) OfficeWriter Home Page
2) OfficeWriter tutorials and Documentation
3) OfficeWriter FAQ
4) OfficeWriter System Requirements
5) OfficeWriter Download Evaluation version P.S.
This is a paid review. However, I have tried to maintain a totally neutral review, and if there is any point that you disagree with me, I would be pleased to hear about in the comments section.

Posted by SQLJason, 1 comment
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
Book Review : MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

Book Review : MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

September 22, 2011

What do you do in your evenings when you are sent off from your base location on a month long consulting assignment? Well, I had an option to soak up the old Beatles numbers in the Cavern Pub (the birthplace of Beatles) every evening, now that I am in Liverpool. But a particular book got me hooked this time and every weekday was just a restless wait for the evening to come. My readers, you might be misled by my words in thinking that I am reading the latest Dan Brown thriller but trust me, I am not exaggerating when I say that I couldn’t keep this book down till I completed it. MDX with Microsoft SQL Server 2008 R2 Analysis Services CookbookNeither the book nor the author needs introduction. The book has already been lauded by most of the industry experts like Chris Webb, Marco Russo, Vincent Rainardi, etc. Tomislav Piasevoli (MVP | blog | twitter) has been one of my favourite bloggers and I have been following him for quite some years now. So it wasn’t quite surprising that I have been following up on the book updates ever since I saw the RAW e-book version (something like a beta release, just has a few chapters) on my PackT online subscription during the month of March. And luckily for me, the launch of the book coincided with my consulting assignment. I have spent quality time with the book most of these evenings (in fact, so much that I think it made my wife jealous Smile) and I am thoroughly impressed with the format of the book. Walking back the memory lane, I always yearned for a SSAS book that had examples of common design and implementation issues and was overjoyed to find the same in Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Once I went through this book completely, I tried to find a similar one for MDX too but in vain (found one for SSRS in the process – Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports). Well, I am glad to say that the search has finally ended with the advent of the MDX cookbook by Tomislav. The book follows a simple and easy to understand structure throughout the 9 chapters : – Getting ready :- getting the pre-requisite queries ready for the task
– How to do it :- how to do the task
– How it works :- how the task works
– There’s more :- alternate ways and more information on the task
– See also :- links to related information and tasks Once I got familiar with the format, I was just reading the ‘Getting ready’ part and trying to solve the task on my own. After that, I would compare my answers with the next 2 sections and it enriched my reading experience. Though the initial chapters were relatively easy, I was scratching my head as the chapters neared the end and I have learned a lot of new things through this book. This is not really intended to be your first MDX book but is a MUST HAVE once you have tasted a bit of MDX. All you MDX developers, you are definitely missing out on a great book if you haven’t read this. Now if you are not sure where to buy it from, here is the link. The Kindle version is also available in Amazon and you can download a sample from here to get a better feel of how the cookbook and its recipes are structured. As for me, I have just added it to the favourites on my blog’s Amazon widget.

Posted by SQLJason, 1 comment