Dan English's BI Blog

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

Posts Tagged ‘powerpivot’

Book Review: DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX

Posted by denglishbi on November 15, 2012

This week I got the opportunity to read a new book out on the market by Rob Collie (PowerPivotPro) – “DAX FORMULAS for PowerPivot: The Excel Pro’s Guide to Mastering DAX”.  I have to admit that once I received the book I read through it in a couple of days, couldn’t put it down and wanted to finish it.  I really enjoyed the style of the book, just like Rob’s blog posts, and there was just a really good natural transition of the content that it kept me hooked.  While reading the book I really felt that I was in a classroom and Rob was presenting the material, something about the writing style, really enjoyed it (hat tip to Rob).

The book is loaded with great tips and advice from Rob, reference links to his blog posts and other content, and real world examples.  The solutions and examples provided in the book can be used right away if you are already using PowerPivot or Tabular SSAS.  There is material in here not just for the Excel pros, but also for IT/BI pros as well.

The second night I was reading the book my son took a peek at what I was doing on my Kindle Fire HD (great reading experience by the way – color, access to the embedded pictures to enlarge, ability to access the links directly, easily take notes and add bookmarks).  My son is in first grade and really starting to get comfortable reading.  He started to read the page I was on:)  It was really cute.  The book was not only good for me, but helped my son learn new words and work on his reading (not sure if this book can qualify for an AR test though)!

When it comes to the editing of the book I did come across a some minor typos, nothing too major, and one figure was missing (Figure 154).  There are references to the Measure Grid (actually Calculation Area in PowerPivot, Measure Grid in Tabular SSAS – this is one thing that I wish Microsoft kept the same or consistent for referencing).  I got a laugh when Rob states that implicit measures are ‘dead to me’ and that he never, ever, EVER creates implicit measures.

The book is really invaluable and the price is a steal (particularly if you buy it on Mr. Excel’s store here)  The length was exactly what I like, short and to the point, I don’t want to read a 800 page door stopper, anything in the 300 or less page range is perfect (that is why I liked the Rational Guide books by Rational Press) and supplemented with links for additional references and more content if desired.  The material and insights that Rob covers is fantastic.  The disconnected table concepts as well as performance tips are PRICELESS.  I would highly recommend this book and without a doubt give it a five star rating.

image

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

Visualizing Data with Power View: Pan-Geo goes PowerPivot

Posted by denglishbi on August 9, 2012

Back in June we released our book on Power View, Visualizing Data with Microsoft Power View.  Not only does the book contain great information about creating reports with Power View, but it also contains content on building tabular models that can be utilized with Power View.  The current release only works with tabular models, so either PowerPivot or Tabular Analysis Services (SSAS).  With our book we primarily focused on building out a Tabular SSAS solution that gets used for the majority of the Learn By Doing exercises.  Sure there are a few times where a PowerPivot file is referenced or we do some initial modeling in PowerPivot, but most of the work gets done with the tabular solution that gets deployed to Analysis Services.image

Well just a few weeks ago when Office 2013 Preview was released I decided to build out the entire Pan-Geo Hospitality & Travel model in PowerPivot.  I went ahead and replicated all of the exercises that we did in the tabular project into PowerPivot (using Excel 2010 and the SQL 2012 PowerPivot add-in).  I then took that same file and upgraded it up to Excel 2013 as well – Power View Meet Excel 2013.

image

So since I took the time to build this out I decided why not go ahead and share:)

If you are interested in Power View and especially if you have already purchased the book (hint, hint) I have gone ahead and uploaded the two Excel files to my SkyDrive here – Power View Book.

  • PGHT Tourism PowerPivot Model 2010.xlsx (Excel 2010 uses the SQL 2012 PowerPivot add-in)
  • PGHT Tourism PowerPivot Model 2013.xlsx (Excel 2013 uses the PowerPivot for Excel 2013 add-in)

Now in order to get a good download of these don’t open the Excel files up in the browser in SkyDrive, simply check the box next to the file and chose the download option on the right-hand side of the screen.  I would do them separately.  Then simply open them up and have at it.  Now if you want to use Power View in Excel you will need to download and install the Office 2013 Preview.  If you want to build out map reports with that you will need to make sure you have an Internet connection so that it can connect to the Bing Map service.

I would be very interested in some feedback and seeing what people build out with the PGHT dataset.  So feel free to leave a comment here or send me a tweet (@denglishbi).

Just an FYI, the Power View book is available on McGraw-Hill Professional, Amazon, Barnes & Noble, Google Books, as well as Kindle, Nook, and other eBook formats and sites.  Please check it out and leave us your feedback and comments:)

Enjoy!

Posted in PowerPivot, Reporting Services | Tagged: , , , | 2 Comments »

Excel PowerPivot SQL 2012 vs. Excel 2013 Performance Demo

Posted by denglishbi on July 30, 2012

I just wanted to do a blog post to demonstrate a performance situation I ran into last week.  I had created a PowerPivot model in Excel 2013 and it performed very well (quite the understatement, phenomenal) when creating PivotTables and Power View reports.  I then went ahead and recreated the model in Excel 2010 with the SQL Server 2012 PowerPivot add-in.  The model creation went fine, but when it came to trying to replicate the report I ran into a slight snag.  Well I actually couldn’t even complete the report, I ran out of memory on a box with 8GB of memory.image

We decided to upgrade the box and gave it an additional 4GB of memory, but that didn’t change anything, the query still consumed all of the memory and wouldn’t complete.  We decided to test this out and migrate the model to a Tabular SSAS instance on a server with 64GB of RAM.  Well the query for the PivotTable just kept running and we watched the Analysis Services process climb to over 18GB and it was still climbing with no results coming back.  Needless to say I was confused and a bit embarrassed.  I decided to give it a go in Excel 2013 again and once again no issues and the results were very fast (might even say blazing fast).

To demonstrate this I have gone ahead and created a demo of the exact scenario with a very comparative sample so you can get a feel for what I was seeing, just take a look at this video:

Excel PowerPivot Performance Demo from Dan English on Vimeo.

In reaching out to the product team I discovered that in Office 15 there have been some performance improvements when it comes to dealing with filters.  Well we can definitely see that based on my results:)  It turns out that these same performance improvements will be coming eventually to the current version as well, we just need to wait for the release of SP1 for SQL Server 2012.  Some of the improvements seen will also be available to querying the data in Analysis Services as well, both Tabular and Multidimensional!  So just hold on until SP1 is released.  If you want to checkout the performance improvements today have a go with PowerPivot for Excel 2013 (you will not be disappointed).

Posted in PowerPivot, SQL Server | Tagged: , | 2 Comments »

Upgrade PowerPivot SharePoint from SQL 2012 RC0 to RTM

Posted by denglishbi on May 21, 2012

So last week I was doing a presentation on Self-Service BI with SQL Server 2012 at the TechFuse MN technical conference and I realized that my demo virtual machine was still running SQL Server 2012 RC0.  I was thinking that I shouldn’t do my presentation and demos using RC0, so why not upgrade.  Probably not the best idea to do this just a few days out from a presentation, but I had my fingers cross and just went for it.

I found some guidance from a thread in the forums by Jen Underwood here Upgrade in place RC0 to RTM and decided to go for it.  I did the in-place upgrade and my SharePoint virtual machine with SQL 2012 was still functioning after I completed all of the steps.  Then I moved on to testing my integrated SSRS setup and realized that the Power View reports were not working and neither were my data sources.  The quick fix for that was to go into Central Administration and go into the properties of the Reporting Services application and click OK.  That completed the upgrade process for my shared service and my Power View reports functioned properly after that.

Next was to verify my PowerPivot was still working.  I opened up my PowerPivot files and uploaded them to SharePoint.  The snapshots appeared in the PowerPivot Gallery just fine, but I was unable to work with the files (the slicers did not work, I received error messages as shown below).  I also went into Central Administration and tried to checkout the PowerPivot management dashboard.  In doing this I received the same error message below:

image

An error occurred during an attempt to establish a connection to the external data source. The following connection failed to refresh: PowerPivot Data

I wasn’t quite sure how to proceed, but after the fix for the SSRS shared service I was thinking that maybe I needed to do the same for the PowerPivot application.  SO I decided that I would try to re-run the PowerPivot configuration tool which is new with the SQL Server 2012 installation.PowerPivot Configuration Tool

The following are the screenshots of running the PowerPivot Configuration Tool with the SQL Server 2012 RTM installation software against my virtual machine that was RC0:

image

image

image

image

image

image

image

Once that was done I went back into Central Administration and checked out the PowerPivot Management Dashboard again:

image

Everything was working as expected again and no more error messages! Victory!

That was quite the relief and I was able to do my presentation with my newly upgraded RTM release of SQL Server 2012, not bad for doing an in-place upgrade just a few days prior to the big event.  This included three instances of SSAS (multidimensional, tabular, and PowerPivot), the SQL database engine, SSRS, SSRS SharePoint integrated, SSIS, and DQS as well.

Here is some additional information if you are looking to do the upgrade for DQS – Upgrade DQS: SQL Server 2012 RC0 to SQL Server 2012 RTM.

Posted in PowerPivot, SharePoint | 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 »

Using Database Images in Tabular BI Semantic Models with Power View

Posted by denglishbi on December 9, 2011

Now that the SQL Server 2012 RC0 release is out I wanted to do a follow up posting to my Using Images in Tabular BI Semantic Models with Crescent (now officially known as Power View).  With this release there is now full support for using images stored in the database – binary data type support has now been added to model development in PowerPivot and Tabular Analysis Services projects.

In this example I will use the AdventureWorksDW database sample again that I used in the previous posting.  First I will load the data into a model using SQL Server Data Tools (formerly known as Business Intelligence Development Studio – BIDS) inside a Tabular Analysis Services project.

image

Nothing new here except for the fact you will notice some changes in the Properties area for the column compared to what it looked like in the CTP3 release where now we have two sections – Basic and Reporting Properties.

If we just go ahead and deploy the project as-is without making any changes what will happen?  Will we be able to start using the LargePhoto column?  Let’s take a look.  If we deploy the project and then setup a BI Semantic Model Connection in SharePoint

image

And then launch Power View

image

We will see that the LargePhoto column is not available in our Field List to select from

image

And yes, now the field list is in alphabetical order! Awesome.

So how do we make this column available?  Let’s go back into the model design.  In order to make this column available we need to do two things.

First we need to set the row identifier property on the Product table and we will do this using the ProductKey column and set that property to True.

image

Next we need to set the default image property on the LargePhoto column and change this value to True.

image

Now we deploy our model again to our server and refresh the browser to reconnect to our model connection.  Now what do we see in the field list?

image

We now see the LargePhoto column and we can use this in our visualizations that we add to the Power View canvas.

image

Tada!

Now how do we do this in PowerPivot with the RC0 add-in?  Well this has changed a bit as well now as far as the UI.

First you need to switch to Advanced Mode in the PowerPivot Window

image

Now with the Product table selected switch to the Advanced tab in the Ribbon and click on the new Table Behavior option in the Reporting Properties section

image

Now we will get a new dialog box where we will need to set the row identifier and default image properties for our Product Table

image

Now that we have this set we can upload this to our PowerPivot Gallery and launch Power View

image

And now we should see the exact same list and options like we had with our Tabular project

image

Success!

And that is the quick update I wanted to post in regards to the change in RC0 for support for database images and to show you how some of the property and menu options have changed.

Enjoy and don’t forget to get your copy of SQL Server 2012 RC0 so you can start testing it out and planning your roadmap change to the next release of SQL Server.

If you are looking for resources and information to the RC0 release check out this great list that Nick MacKechnie put together here – SQL Server 2012 RC News.

Posted in Reporting Services, SQL Server | Tagged: , , , | 1 Comment »

PASSMN October Monthly Meeting Follow Up

Posted by denglishbi on October 25, 2011

Well another month is coming to an end and this past week I did a presentation on “An Introduction to BI Semantic Model & Delivering Self-Service Reporting with Power View (Crescent)”.  So the momentum from the PASS Summit continued to roll on and we continued with the SQL Server 2012 content and providing insights into what is coming showcasing the new features in Analysis Services, PowerPivot, and showing the new feature that will be available in the Reporting Services SharePoint 2010 Integrated Mode – Power View!

If you were not able to attend, no worries, you can check out the recording, watch some short video clips of the demos, and take a look at the presentation content on SlideShare.Minnesota SQL Server Users Group

Intro to BI Semantic Model & Delivering Self-Service Reporting with Power View from Dan English on Vimeo.

PowerPivot Denali CTP3 Demo from Dan English on Vimeo.

Project “Crescent” Demo from Dan English on Vimeo.

Intro to BI Semantic Model & Self-Service Reporting with Power View

Next up is ScarePoint Saturday to do a “Inside PerformancePoint” presentation and then onto MN SQL Saturday #99 to do the “Inside PerformancePoint” presentation again!

I mentioned a setup of using XBox Kinect with SQL Server 2012 at the user group. There is a video of this if you want to check it out on YouTube here – “SQL Kinection – Control SQL Server 2012 with gestures using Microsoft Kinect!”

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

Video: PowerPivot Denali CTP3 New Features

Posted by denglishbi on September 20, 2011

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):image

  • 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!

Posted in PowerPivot | Tagged: , , | 1 Comment »

Video: Leveraging PowerPivot Demonstration

Posted by denglishbi on April 4, 2011

I finally got around to recording the presentation.  I only did the demo to create the screenshot application I included in the presentation, I didn’t include the portions where I demo exploring the xlsx file by renaming it to zip file extension or browse the VertiPaq folders that get created in your %temp% directory in Windows (watch out for these because they can take up a lot of space over time – I currently have 16 VertiPaq folders consuming 1.31GB of space).image

For the follow up on this presentation you can check out my previous posting – MN Microsoft BI User Group 2011 Q1 Follow up.  You can download the PDF presentation material by clicking on the PowerPoint screenshot on the right-hand side which I have stored on my SkyDrive account.

This video file is approximately 27 minutes long.  I didn’t realize my demonstration was that long, next time I will have to time this out so I can make sure I have plenty of time to show the complete demo and have time to resolve any technical difficulties.  The file is approximately 43MB if you download it and I did make this in HD mp4 quality, so it should be good to watch.  This was my first time using Camtasia to create a screencast, so hopefully everything worked out okay (really sweet product).

Leveraging PowerPivot Demonstration from Dan English on Vimeo.

Hope you enjoy the video, please feel free to post comments and questions.  Later.

Update (4/5/2011):  I have the PowerPoint presentation on SlideShare if you don’t want to download it and just want to view the slides.

Update (4/6/2011):   In regards to the CSV file I simply used the Export wizard in SSMS to create the file.  In order to do that I create the following view in the ContosoRetailDW database to reference:

CREATE VIEW [dbo].[vw_SalesBudgetData]

AS

SELECT DateKey, ChannelKey, StoreKey, ProductKey, SalesQuantityQuota, SalesAmountQuota,

FROM dbo.FactSalesQuota WHERE (ScenarioKey = 2)

When I exported the file I also used the Vertical Bar for the delimiter and included the column headers.

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

MN Microsoft BI User Group 2011 Q1 Follow up

Posted by denglishbi on March 30, 2011

Last week was the first meeting of the year for our Minnesota Microsoft BI User Group.  The topic for the whole evening was Analysis Services including talks at getting started with SSAS (UDM) project, some tips & tricks, and then my PowerPivot presentation.

We also tried using LiveMeeting for the first time with recording the presentations and hopefully in the next week or so we will be able to share that content as well.  We will see because it was the first time and I haven’t had a chance to check out the recordings yet.

In general I think the presentations went well and we had around 70 people all together and another dozen or so on the LiveMeeting.  The one thing that didn’t go so well for me was when I had to share my desktop to do my presentation.  For some reason LiveMeeting was forcing me to switch to a 800×600 resolution (haven’t ran into this before, but usually only have to switch to a 1024×768).  Well needless to say when it came time to do my PowerPivot demo it pretty much tanked.  Trying to work with PowerPivot with this resolution is pretty much impossible, especially went it comes time to create the PivotTables and PivotCharts along with slicers in Excel.

If you are curious to what I was experiencing here is what PowerPivot looks like at 800×600:

imageimage

When I initially loaded the data into the PowerPivot window for some reason I was unable to see the tabs on the bottom of the window, so I couldn’t figure out how to switch between the tables.  Once I closed the windows and reloaded it I was able to see the tabs though (nothing like being put on the spot – I felt like an id10t).  Now that I have ran into this issue I figured out a few workarounds for in the future like in Excel I can minimize the Ribbon and utilize the zooming capabilities in the lower-right hand corner.  Once you do this you can get a little more working room (this doesn’t work in the PowerPivot window though).  After you do these modifications you can at least see what you are doing to a certain degree:

image

There were quite a few spots where I couldn’t even position the dialog boxes in a position where I could even click the buttons in them, so I was just hoping that if I clicked ‘Enter’ that the dialog box would close and accept my changes I made (and they did, thankfully).  It is also hard to find items in the Ribbon when it gets squished down, so you just have to be pretty much prepared for anything when it comes to doing a live demo.  I tried to salvage the demo as best as I could, but eventually I had to give up and toss in the towel.  I was extremely disappointed and felt awful for the attendees.  I am hoping that what I was able to show along with the content that people found it useful.  I am planning on creating a video of my PowerPivot demo to share as well.  Once I have this available I will post an additional follow up posting with that as well.image

For now I will simply provide some of my blog postings as references for some of the items I mentioned in my presentation and share my presentation as well.  Here are the blog posting references to some items I covered:

I do have more postings as well, you can simply check out the items through the PowerPivot tag.

Here is my presentation deck (available from my SkyDrive account) and stay tuned for the video; I will have it available soon and post on Vimeo with my other videos and post a follow up here on my blog.  Until then, enjoy the presentation content.

image

Posted in Business Intelligence, PowerPivot, Training | Tagged: , | 3 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 1,754 other followers