Denali in PowerPivot: Self-Service BI hits the Big Time – December 12, 2011
You must register at https://www.clicktoattend.com/invitation.aspx?code=158248 in order to be admitted to the building and attend.
Denali in PowerPivot: Self-Service BI hits the Big Time
I’ll admit it – I wanted to like the first release of Microsoft PowerPivot. I realized it was a 1.0 version, and wasn’t intended to be a full replacement for Analysis Services. In the last year, I trained people on it, and I implemented it for a customer – but I wanted more. Well, borrowing from S.E. Hinton’s “That was Then, This is Now” theme, Microsoft is releasing a new version of PowerPivot in SQL Server Denali, and PowerPivot 2.0 is a remarkable and substantial upgrade over PowerPivot 1.0.
I’ll take a stripped down version of an actual production database and demonstrate “corporate BI” with PowerPivot 2.0. Along the way, I’ll focus on some of the enhancements in PowerPivot 2.0, including the new Diagram View and Hierarchy designer, the ability to create multiple relationships, the new Measure Grid to create actual measures, as well as other welcome additions (such as “Sort by” columns and drillthrough capabilities, to address some of the shortcomings in PowerPivot 1.0). I’ll also construct a data-driven KPI, using the new KPI features in PowerPivot 2.0.
Microsoft continues to leverage existing Analysis Services features in PowerPivot 2.0 – I’ll demonstrate two such features, perspectives and aggregations.
I’ll also show some of the new DAX features in PowerPivot 2.0. PowerPivot 1.0 introduced the new DAX programming language, which is a hybrid of Excel macros and MDX. PowerPivot 2.0 features 30 new DAX functions, such as ranking/statistical functions, filtering functions, and lookup functions. I certainly can’t show all 30 new DAX features, but I will certainly demonstrate a handful of them. I’ll also offer a few performance tips regarding the use of certain DAX functions.
Additionally, I’ll show a scenario where we can implement dynamic role security with the new PowerPivot DAX function UserName(), to limit visibility of data based on the current domain user name – something that is very critical in many corporate settings, and was also lacking in the first version of PowerPivot.
Finally, I’ll show how to publish PowerPivot content to SharePoint 2010, and how to schedule a data refresh and verify the refresh using the PowerPivot Management Dashboard in SharePoint.
Kevin S. Goff
Kevin S. Goff is Microsoft SQL Server MVP and has been a recipient of the MVP award since 2005. He is the SQL Server/Business Intelligence Practice Manager for SetFocus, a Microsoft Gold Partner for Learning Solutions. Kevin writes regularly for CoDe Magazine on different SQL Server/BI topics, and speaks regularly in the Mid-Atlantic region at CodeCamp, SQL Server, and SharePoint Community Events. He was the lead author on one book, a contributing author on a second book, and has just started his third book. His email address is firstname.lastname@example.org, and his website/blog is http://www.kevinsgoff.net
12-12-2011 6:00 P.M. – 8:30 P.M.
Microsoft NYC offices, 1290 Avenue of the Americas, 6th Floor, New York, NY, 10104