Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for February, 2012

Minnesota Microsoft BI User Group 2012 Q1 Meeting–Mar 6

Posted by denglishbi on February 22, 2012

The next MN Microsoft Business Intelligence User Group has been scheduled and the agenda is set. This meeting we will have two sessions starting out with a Hennepin County Medical Center case study and then going over the new features in SQL Server 2012 PowerPivot. MN Microsoft BI User GroupJust a reminder that the location for this event has now moved to the Microsoft Technology Center location in Edina.

MN Microsoft BI User Group 2012 Q1 Meeting

Sponsored by:

Microsoft - Annual Sponsor

Date: Tuesday, March 6, 2012

Check In: 2:30 PM Event Time: 3:00 PM – 7:00 PM

Location: Microsoft Technology Center – 3601 76th St W, Suite 600 Edina MN 55435 (in LOW 1, 2, and 3)

To join via Live Meeting: Click Here

Please Register Now

Agenda:

  • 2:30-3:00 : Registration
  • 3:00-3:15 : Microsoft BI User Group Updates & Announcements
  • 3:15-4:15 : HCMC Case Study – Healthcare BI
  • 4:15-4:30 : Break
  • 4:30-5:30 : Get the most out of DAX & PowerPivot for SQL Server 2012
  • 5:30-7:00 : Social and Networking

Presentations:

Case Study: Hennepin County Medical Center (HCMC) – Healthcare BI (by Paul Volkmuth, HCMC; Mark Chisholm, HCMC; Mark Knutson, HCMC; Fazel Haris, Stratezon) – Healthcare presents some unique challenges in creating a BI infrastructure. Hennepin County Medical Center (HCMC) has created an innovative EDW to allow Knowledge Users to interactively query clinical information to gain insight into patient care, cost and payment. This session is a Case Study of our journey of developing a BI solution from the ground up using the Microsoft BI tool stack.

Get the most out of DAX & PowerPivot for SQL Server 2012 (by Paul Doyle & Greg Beaumont, GNet Group) – In this presentation we will cover how new functionality in PowerPivot for SQL Server 2012 supports personal, team and organizational BI. We will demonstrate standard DAX formulas for handling time intelligence, many-to-many relationships and security. We will also cover hierarchies, KPIs and PowerPivot visualizations so you are equipped to get started answering business analytics questions

Please Register Now

Hope to see you there!

Posted in Business Intelligence, Training | Tagged: , | Leave a Comment »

PASSMN February 2012 Meeting

Posted by denglishbi on February 17, 2012

UPDATE (2/21/2012): New presentation information – local author Brian Larson will be speaking

The next Minnesota SQL Server User Group meeting is coming up next week.  This month author Brian Larson will be speaking on using maps in Reporting Services and you will see not only how it can be used its analytical capabilities, but also for gaming.  Just a note that this month the meeting will be from 4 to 6 p.m. and don’t forget the new meeting location which is at the Microsoft Technology Center in Edina.

Minnesota SQL Server Users Group

The sponsor for this month’s meeting is True Source Recruiting of Minneapolis, Minnesota.

True Source Logo

Location: 3601 West 76th Street, Suite 600 Edina, MN 55437

Date/Time: Tuesday, February 21 from 4 to 6 p.m.

Live Meeting:

Agenda:

  • 4:00-4:15 : Registration / hospitality / networking
  • 4:15-4:30 : Announcements etc.
  • 4:30-4:45 : SQL Tips/Tricks
  • 4:45-5:45 : Geographic Visualizations Using Maps in SSRS (Brian Larson)
  • 5:45-6:00 : Closing comments/prize giveaways

Please click here for meeting details and to RSVP for the event

Geographic Visualizations Using Maps in SSRS (Brian Larson, Superior Consulting Services – VP of Technology) – This session will explore the many and varied uses of the map report item in Reporting Services. From providing geographic analytics to diagramming locations on a warehouse floor plan, the map report item – when combined with SQL Server spatial data types – can be used in a variety of ways to create spatial representations of your data. We may even look at a game or two created using the map report item.

Posted in Training | Tagged: , | Leave a Comment »

Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0

Posted by denglishbi on February 9, 2012

Last year I did a posting on Using PowerPivot with PerformancePoint Services (PPS) 2010. I thought it would be a good idea to do an updated posting with the new release of PowerPivot that will be coming out this year to show the new features that you will be able to take advantage of now.

Before we get started I need to point out that if you setup your SharePoint environment with a fresh install of SQL Server 2012 that you will also need to download and install the Analysis Services ADOMD.NET (version 10.5) data provider from the SQL Server 2008 R2 SP1 Feature Pack.

1033\x64\SQLSERVER2008_ASADOMD10.msi

I had previously tried to reference PowerPivot SQL Server 2012 in the “Denali” CTP3 and in RC0 release and received the following error message when trying to configure the connection string in the PerformancePoint data source setup:

An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.

I was glad that after reporting this that a resolution was found and that simply installing the older version of the ADOMD.NET provider was able to bridge the gap to the new version of the PowerPivot files with PerformancePoint – You Can Use SQL Server “Denali” PowerPivot Models as PerformancePoint 2010 Data Sources.  After you install the version 10.5 release of the data provider you will need to perform a iisreset after the installation to get this working properly in your environment so that you can create the data source in Dashboard Designer.  The previous version of the ADOMD.NET data provider was not needed to connect to the new Tabular version of SSAS, just for PowerPivot.

What’s New?

Okay, so now that we got that covered what are we able to do with the new release of PowerPivot?  Let’s take a look and see what has changed.

New Features Available in PowerPivot (disclaimer: this is based on the SQL Server 2012 RC0 release of PowerPivot) to use with PerformancePoint:

  • Hierarchies! – yes, now we can define hierarchies in our data model such as Calendar with Year, Quarter, Month, and Date levels or Products with Category, Subcategory, and Product levels. In order to add hierarchies you have to use the new Diagram View that is another new feature to see a visual representation of your data model.  In addition to this you can also setup Parent-Child hierarchies as well with some of the new DAX capabilities with the PATH and PATHITEM functions to setup your levels and get at the relationships.
  • Measures and formatting – measures are defined in the PowerPivot Window and part of our model. Another very nice feature that we can do now is set the format for measures that we define in the data model. So if we want to display the sales or profit we can do this in currency or if we want to display gross profit margin we can do that as a percent and we can specify the comma separator and number of decimals as well.
  • Column sorting – in the R2 release of PowerPivot you might have noticed that everything was sorting alphabetically. So if you worked with say days of the week or month names you would have had to come up with some alternate naming conventions to get the values to display in the correct order that we are used to seeing them. Well in the 2012 release we now have the ability to base the sort order of a column off another columns, just like we can do in the Multidimensional Analysis Services development. So if we have a column for month number of year we can use that to properly sort the month names and we no longer have to incorporate the month number into the description that we display in our reports.
  • Key Performance Indicators (KPIs) – you can define these now in your data model, but unfortunately at the time of this post you are not able to import KPIs from Tabular data models.  There is currently an open bug on connect related to this issue – can’t make PerformancePoint scorecard by importing tabular KPIs (feel free to vote this one up).
  • Drillthrough (show details) – there is now drillthrough support for Tabular data models, so you can use the show details options on measures in your Analytical Reports. Granted you don’t have any abilities to customize the drillthrough output in PowerPivot models (Tabular will have this thanks to Greg Galloway and BIDS Helper), but you can see the detail data in the table that makes up the number.

Hierarchies

In the new Diagram View we can now right-click on a column in one of our tables and select the context menu option ‘Create Hierarchy’

image

We can then provide a meaningful name to the hierarchy (something besides Hierarchy1) and drag additional columns down into the hierarchy to add levels for users to provide the navigation path.

image

Now in this example I already used the RELATED function to pull in the product category and subcategory names into the product table.  I have also hidden the ProductSubcategory and ProductCategory tables from client tools so they will not be visible to the users when the data model is deployed (in the first image you can see that those tables are dimmed out, more transparent than the product table).  Now I might want to go ahead and rename the last item in my hierarchy here so that it is a little more cleaner.

image

In doing so you can see that in parenthesis I can still see the actual column in my data that it is referencing.  Now I could go ahead and hide the individual source columns that are being represented in our hierarchy.  This would force the users of the model to have to use the hierarchy to navigate the data.  That would be fine in most cases, but if you are going to use this data model with Power View you will want to keep them in the model since the initial release of Power View in SQL Server 2012 will not support hierarchies.  So if you hide them then you will not be able to see those columns or the hierarchy in your model and the users would not be able to properly analyze and report on the data.

In addition to the typical user defined hierarchies I also mentioned there is support for parent-child hierarchies.  So if you have a self-referencing table such as an Employee table that contains EmployeeKey and ParentEmployeeKey columns you can utilize DAX to establish the hierarchy path for each row and then defined the levels like the following:

image

=PATH(Employee[EmployeeKey], Employee[ParentEmployeeKey])

And once you have that setup you can setup additional calculated columns for each of the levels in the path that can then be used back in the Diagram View to establish the hierarchy just like the Products one above.

image

=LOOKUPVALUE(Employee[EmployeeName],Employee[EmployeeKey], PATHITEM(Employee[EmployeePath], 1,1))

image

Measures and Formatting

Another nice feature is the ability to designate the format output of the measures you define.  In the R2 release of PowerPivot you had to do this in Excel where you also created the measures and it was not actually part of the data model.  Well in this release not only is this capability part of the data model, but so are the measures.

First of all you have the ability to create and define explicit measures in your data model now.  There is a new portion in each table in our PowerPivot Window called the Calculation Area.  This is where we can define measures that will then become part of our data model.  Granted measures can still be created in Excel, but in doing this they will not get added back into the data model and exposed in the PowerPivot Window.

image

Now that we have some measures defined we can go ahead and add formatting to each of these by selecting the measure and using the Formatting section in the Home tab of the Ribbon or right-clicking on the measure and selecting Format… to open up the Formatting dialog box.

NOTE: when using PowerPivot with tools like PerformancePoint, Report Builder, and Report Designer (BIDS) you will need to define explicit measures.  Implicit measures will work in Excel with PowerPivot and in Power View, but not with these other tools.

image

The Calculation Area is a free form area and you can enter measures in any of the cells and the syntax you use is Measure Name:=[DAX Expression]

In the first screenshot above where I have the Reseller Total Sales measure I could actually take that and enter that in the Calculation Area in another table if I wanted.  In doing so I would have to alter the DAX a bit and make a direct reference to the table name like such – Reseller Total Sales:=SUM(ResellerSales[SalesAmount])

Column Sorting

In the v2 release of PowerPivot we now have the ability to define the way a column is sorted based on another column.  So for example in my Date table if I have a column called EnglishDayNameOfWeek with values Sunday, Monday, Tuesday, etc. and I have another column called DayNumberOfWeek with values 1, 2, 3, etc. I can set the sort order of the first column to be based on the second column.  If we did not do this then the names would simply be sorted alphabetically like they were in v1 of PowerPivot.  In v1 of PowerPivot you could simply sort the values in Ascending order in a PivotTable to correct this, but in Slicers you did not have that ability and would have to typically setup a calculated column to sort the values properly like 1 Sun, 2 Mon, 3 Tue, etc.

image

Once you have the column selected you can then select the Sort by Column option in the Ribbon.  This will then open up the Sort by Column dialog box where you can specify the column to be sorted and then which column defines how it should be sorted.

image

You can use this to not only get typical items sorted properly like the days of the week and month names, but you could also use this to define custom report sorting that users would like to see.  So say you have predefined names you use to categorize your customers based on their sales like Platinum, Gold, Silver, Bronze, etc. and this is how you want them displayed on your report (in this particular order).  Well now you can be defining another column in the table that has the values 1, 2, 3, 4, etc. respectively and setup the Sort by Column.

Key Performance Indicators (KPIs)

Defining KPIs in the PowerPivot models is a new feature that is available.  As I mentioned though, currently you are not able to import these into PerformancePoint to create an Analysis Services scorecard like you can with Multidimensional Analysis Services.  As an example though, here is what you can do if you want to set this up so that you can use them in Excel (which could then be displayed with Excel Services in your dashboard) or with Reporting Services (which could also be displayed in your dashboard).

First you need to define an explicit measure like we already did above with Reseller Total Sales.  Once you have this selected you can then click on the Create KPI option available in the Measures section of the Home tab in the Ribbon or you can right-click on the measure and select the option to Create KPI…  This will then open up the Key Performance Indicator (KPI) dialog box.

image

In the dialog box you can then specify the target to compare the measure to and this can reference another measure in the data model or an absolute value.  Once you have that defined you can then adjust the thresholds and select your icon styles.  Make sure you pick an appropriate icon style that not only has a color option, but also a shape to differentiate it because as I have noted before, there are people that are color blind (so do not just go with the default first icon style selection).

The other thing that you can do is specify some descriptions for the KPI (descriptions are another new feature and these are available on other items such as tables, columns, and measures).  The descriptions are then displayed to the end-user in say Excel when they are working with the PowerPivot field list and hovering over the items.

image

Once you have this setup you will notice a new icon next to the measure in the Calculation area (and in the Diagram View) which signifies that it is now configured as a KPI.

image

Just remember, you cannot import these currently into PerformancePoint, so you will still have to manually setup the KPIs.  Hopefully this will be resolved, just watch the Connect item link I provided above and feel free to vote it up as well.

Drillthrough (Show Details)

Not much to say here other than the fact that you can now get to the detail records that make up a particular measure value when displayed in Excel and in PerformancePoint.  So in PerformancePoint if you have the KPIs setup with the Data value calculation instead of the Default or you are displaying the measures in an Analytical Report you will now be able to select the Show Details option and actually display the individual rows that are in that table that make up the value.

The End Result – Dashboard in PPS

Okay, so now we know what the features are we can use, what do they look like in PerformancePoint.  Let’s take a look at that.

image

And last but not least….

image

Pretty sweet and I am looking forward to the new feature support.

If you are looking for the downloadable sample files for SQL Server 2012 take a look here SQL Server 2012 Samples ReadMe.

Posted in PerformancePoint, PowerPivot, SharePoint | Tagged: , , , | 10 Comments »

Free SQL Server Community February 2012 Training

Posted by denglishbi on February 2, 2012

Head over to Pragmatic Works site and take a look at all of the training that is available for February – free training on Tuesdays and Thursdays at 11 a.m. EST. Free Training on the T's

  • February 2, Anish Sana – SQL Server 2012 SSIS: New and Improved Features
  • February 7, Herve Roggero – SQL Azure and SqlBulkCopy
  • February 9, Cathy Eng – Moving On Up: Positioning Your Resume Above Your Competition
  • February 14 (don’t forget, Valentine’s Day), Angel Abundez – Chart Anatomy 101 
  • February 16, Dan Clark – Building Your First Tabular Model
  • February 21, Mark Brown – Beginning Development for Business – Part 4: Running in the Halls
  • February 23, Lara Rubbelke – Microsoft Big Data and Apache Hadoop
  • February 28, Brian Knight – Managing SSIS in Denali

If you miss a session they record them and you can check them out later on-demand in the webinar resource area.

So what are you waiting for, get registered for one of these spectacular webinars today – Webinar Registration.

Don’t forget, you can stay up-to-date on the training events through the PWFreeTraining twitter account as well.

Posted in SQL Server, Training | Tagged: | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 1,758 other followers