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

[tweetmeme source=”denglishbi” only_single=”false”]

A couple of weeks back I mentioned a new book on the market related to MDX and Analysis Services and that I had received a copy to review.  The book is MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook written by Microsoft SQL Server MVP Tomislav Piasevoli.

This is by no means an introduction (101) book to MDX, but it is an MDX reference book that anyone using Analysis Services should have on their desk (or device – available in eBook version as well).  I have not read a cookbook technical style of book before and I simply couldn’t put it down.  I really enjoyed the style of here is a situation, here’s how you do it, and now let’s see how this actually works.  But the author doesn’t just stop there, nope, but what there’s more information about the recipe and then provides additional reference links to checkout or other spots in the book to look at as well.  After I got done reading one recipe I continued on to the next one because I was interested in reading the solution and finding out more and seeing how it compared to how I would approach the situation.

I found the book very easy to read and the information is extremely valuable for anyone working with Analysis Services and trying to solve solutions using MDX. This is a must have book for any SSAS professional, if you don’t believe me, check it out for yourself and you will definitely agree, just like others already have like Derek Goodridge (@WorkerThread) and Jason Thomas (@de_unparagoned).

Book link – MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook.

I would definitely rate this a must have and give it 5 out of 5 stars – .

MN Microsoft BI User Group 2011 Q3 Follow up

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (9/23/2011): A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview.

Last week I presented on What’s New with BI in SQL Server Denali (SQL11) at the Minnesota BI User Group Q3 meeting with Jin Cho from Microsoft.  Granted that in only an hour and a half we couldn’t do the topic complete justice because there is just so much to cover and it helps to provide some demos to showcase the new features and enhancements that have been made.  The topics that we covered where going over Analysis Services and PowerPivot, talked about the BI Semantic Model, Columnstore Indexes, Integration Services, Data Quality Services, Reporting Services SharePoint Integration, and “Crescent”.

I wish I would have had more time to be able to demo some of the new SSIS items, but I allocated my demo slot to show the new self-service reporting authoring tool Crescent.  Each of the topics that we did talk about can easily be their own topic for presentation, so stay tuned for the upcoming MN SQL Saturday event and the next MN Microsoft BI User Group Q4 meeting in December.  There will also be a lot of content in regards to this topic at the upcoming PASS 2011 Summit next month that I would highly recommend.

You can download the presentation from my SkyDrive account or you can check out the slides available via SlideShare as well:

I have also setup a video that goes over the “Crescent” demo that I went over during the presentation.  I believe that when I switched laptops during the presentation that I did not share the desktop with Live Meeting, so I apologize for that and since this will not be part of the recording I put together a quick video.

Project “Crescent” Demo from Dan English on Vimeo.

A link to the Project “Crescent” Demo video has now been added and featured on the “Crescent” wiki page in the Videos section – Project Crescent Overview

For more BI highlights in the upcoming SQL Server release check out this list – Top 10 BI Reasons to Upgrade to SQL Server ‘Denali’ (SQL11).

Video: PowerPivot Denali CTP3 New Features

[tweetmeme source=”denglishbi” only_single=”false”]

I put together a video demonstration as a follow up to last week’s Minnesota Microsoft BI User Group Q3 meeting.  In this video I highlight some of the key enhancements and additions that have been made to PowerPivot in the CTP3 release for SQL Server “Denali” (SQL11).  This add-in for Excel 2010 is available to download and test out from the Microsoft Downloads area here.

The main items covered in this video demo are the following (and there might be a couple of others that I touch on as well, so listen closely and follow along):

• Measure Grid
  • Create measures inside model
  • Format measures
  • New DAX Functions
• Sort by Column
• Diagram View
• Hierarchies
• KPIs
• Drillthrough

PowerPivot Denali CTP3 Demo from Dan English on Vimeo.

 

Hope you enjoy the video and stay tuned for more!

PASSMN September 20, 2011 Meeting

[tweetmeme source=”denglishbi” only_single=”false”]

The next Minnesota SQL Server User Group (PASSMN) meeting is coming up in a next week and this month Ajay Nangia from Microsoft will be presenting on some new SQL Server ‘Denali’ features coming out – AlwaysOn and Columnstore Indexes.

The sponsor for this event is Visi:

• Sponsor: Visi
• Date: September 20, 2011
• Time: 3:00 – 5:00 PM
• Location: 8300 Norman Center Drive, 9th floor, Bloomington, MN 55437
• LiveMeeting URL: https://www.livemeeting.com/cc/usergroups/join?id=P7WSKB&role=attend&pw=Q%40bS%2698%40t
• Please click here for meeting details and to RSVP

Agenda:

• 3:00-3:15 : Registration / hospitality / networking
• 3:15-3:30 : Announcements etc.
• 3:30-4:45 : Introduction to Denali AlwaysOn and Column Store Technologies
• 4:45-5:00 : Closing comments/prize giveaways

Presentation:

Introduction to Denali AlwaysOn and Column Store Technologies (Ajay Nangia, Microsoft) – The session will talk about the new High Availability and Disaster Recovery solution being introduced in SQL Server “Denali”. It will provide an overview about the new features and capabilities such as utilization of secondary databases for a better return on hardware investment and how to simplify HADR deployments. It will also introduce the new column-based accelerator for vast improvements in query performance.

Unfortunately I will not be able to attend because of another commitment, but definitely some great content to check out if you can make (or use LiveMeeting if you are out and about).

Using Images in Tabular BI Semantic Models with Crescent

[tweetmeme source=”denglishbi” only_single=”false”]

One of the really neat features with the new Reporting Services authoring tool code named ‘Crescent’ is that you can reference images directly from your model.  This can look really cool when you use the images in a card visualizations, part of a table, as tiles, and as slicers to use as filters within the report.  Here is a nice report that is part of the Hello World Picnic Sample that came out with the CTP3 release of SQL Server ‘Denali’ (download from Project Crescent Samples and download images from Sample Images and you can find information on the Crescent Samples for CTP3 in the readme here).

So this is really cool and provides a really nice visualization perspective to the report both from what you are looking at and to add a little color and life to the report.  When you are looking at a sales report for products, wouldn’t it be nice to see the image of the product or category that you are looking at.  So what better place to store this information than in your BI Semantic Model that you can then leverage with your reporting tool like Crescent.  So how do you do this?

Well in the current CTP3 there are a couple of different ways that you can go about this.  The first option is to store your images in say a SharePoint library and the provide the Image URL references in the model.  That is how the example above is being done.  That report is actually leveraging a PowerPivot Tabular model that was built in Excel 2010 leveraging the CTP3 release of the PowerPivot add-in which you can download here.  The second option that you have right now is to store the images in a database table and reference that way use base64-encoded strings.  Getting this option to work in CTP3 is by no means straightforward either and took me quite a while to figure out.  Just using a base64-encoded string was a puzzle and luckily Robert Bruckner provided that solution as to how to format the binary data in the database to this format.  There is a third option which is simply referencing the binary data from the database, but currently the CTP3 release of Crescent will not recognize this field type in the model at all.

So lets take a look at the two working options we have that we can use with the CTP3 release of Crescent along with the Tabular BI Semantic Models using both a PowerPivot and a Tabular Analysis Services solution as examples.

Image URL

As I mentioned, the Hello World Picnic PowerPivot model uses this setup.  So I will highlight how this is done.

• First you need to have images available for what ever you want to display.  The download link is available above for the samples and then the instructions are provided in the information readme.
• Next you need to place the images in a location that can be referenced as a URL in the model.  In this example they are placed in a document library within a folder.

images are stored in a SharePoint document library called Shared Documents buried down a couple of levels –> /Images/HelloWorldPicnic/

• Then within your table in the PowerPivot Tabular model you can provide your URL to the image.  In this example they store the name of the image in the table and then use a calculated column to generate the Image URL.  In order to activate the option to set this as an Image URL in the Reporting Properties (which is in the Advanced tab – might need to turn on the Advanced Mode in the file setting first) you need to define the Table Identifier column in the table.  This means that you need to specify which column makes the table unique to identify each record (in this example it is the ItemId column).

need to specify a table identifier – a column that defines the table and makes each record unique

a calculated column is used here that includes the image name column from the table along with the rest of the string to the SharePoint location and concatenates everything together

• Then once you have deployed your file to SharePoint you can create a Crescent report from the PowerPivot file and you will now see the field in the field list to select from along you can start leveraging this in your report design.

the Items Name and Drawing fields are selected from the Field List which get added to a Table

So that one wasn’t too bad.  And this is a nice option because having the images in a document library location is a nice spot where end-users can maintain these as needed and swap them out.

Okay, let’s take a look at the other option that is currently available and that is storing the images in the database and using a base64-encoded string.

Base64-Encoded String

So what if the images are already stored and available in a database table? Well there is a solution that can allow you to do this, at least if you are doing the Tabular Analysis Services project type.

• Here we already have the images stored in a database table in as a varbinary(max) data type – just a warning, I believe that the max size for the images is 25K, I don’t believe that has changed yet, so just be aware of that in case your images do not show up. In this example we will be using the AdventureWorksDWDenali CTP3 sample database leveraging the product dimension data – you can download the sample databases and projects for Denali CTP3 here.

table contains a varbinary(max) column called LargePhoto that contains some images of our products

• Once the table is imported into a Tabular Analysis Services project we need to modify the Table Properties for the product dimension and change the reference from Table Preview to Query Editor to generate our base64-encoded string that we will use in our model instead of the binary data that is already available from the LargePhoto data (this is currently not supported by Crescent right now, but will be in next release).

now the key here is to add the logic to generate the base64-encoded string which is the following – thanks to Robert Bruckner for providing the logic:

cast(N” as xml).value(‘xs:base64Binary(xs:hexBinary(sql:column(“[dbo].[DimProduct].LargePhoto”)))’, ‘varchar(MAX)’) as LargePhoto64

• Next we need to once again set the Table Identifier (Product Id column – in this example the ProductKey column has been renamed to Product Id) and then set the properties on the new LargePhoto64 column for Image URL to True along with Representative Image to True

set the Product Id as the Table Identifier (which is actually the ProductKey column)

set the Image URL and Representative Image properties to True for the LargePhoto64 column

• The next step is the hack that we have to perform to the Code portion because if you try to change the Image URL property back to False it will also set the Representative Image to False and that is not what we want.  You can right-click on the bim file in the Solution Explorer and select ‘View Code’ or pick the option from the ‘View’ file menu.  Once you are in the Code behind do a search for LargePhoto64 and it will take about three times to find the right spot we are looking for which contains the Type property setting that displays ImageURL.  We need to change this to just Image.

find the LargePhoto64 column where the Type property is located with ImageURL

now change the Type so that it does not include the valuens=”ddl300” and remove URL from the value so that it is just <Type>Image</Type>

• Now you want to Save this change and then double click on the bim file in the Solution Explorer to go back into the Design view and deploy the database to your Tabular instance of Analysis Service.
• Setup the BISM Connection File to you model (see my previous posting on this if you are not familiar with how to setup data connections to a Tabular BI Semantic Model – Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration) and then click on the connection file in your SharePoint library and you will now be able to utilize the LargePhoto64 column as an image in your reports.

the Product Name and LargePhoto64 columns were selected from the Field List and added to a Table

So there you go, not too bad, huh?  You have two different options right now for using Images in your models.  In the next release we should see direct support with Crescent in being able to utilize binary data directly instead of having to use the base64-encoded string method.  But for the time being this is what you can use.  My preference would be the Image URL.  This just seems not only more straightforward, but also easier to maintain and update, especially from an end-user standpoint.

Enjoy!