Posts

PowerPivot Configuration Tool

/0 Comments/in /by

One great enhancement in SQL Server 11 (Denali) is the PowerPivot for SharePoint setup refactoring. Previously, the PowerPivot for SharePoint setup and configuration was included and performed at the end of the SQL Server setup. Consequently, it was very difficult to figure out what went wrong. In Denali, you need to perform the PowerPivot for SharePoint configuration as a post-installation step after the SQL Server setup completes. You can do this using the new PowerPivot Configuration Tool, SharePoint Central Administration, or PowerShell.

You can find the PowerPivot Configuration Tool in the SQL Server Denali CTP3 ð Configuration Tools program group. When you open the tool, it examines the SharePoint setup and presents a list of tasks (if any) that need to be done to configure PowerPivot for SharePoint so it’s operational. Once you validate the tasks and run them, the tool would perform the steps one at a time and indicate which step is currently taking place. You can select task and click the Script tab to see a PowerShell script for that task or the Output tab to see an execution log.

If a step fails, the tool stops and the rest of the steps are not executed. You need to examine what went wrong, fix it, and rerun the tool to execute the remaining tasks. You can also use the tool to remove features, services, applications. and solutions.

080811_0126_Transaction1

SharePoint is great product but it’s also very complex and difficult to troubleshoot. The PowerPivot Configuration Tool will simplify the management effort to configure PowerPivot for SharePoint.

PowerPivot Stories from the Trenches

/0 Comments/in /by

Now that a snow blizzard has paralyzed Atlanta for a week, what a better way to start the new year than sharing a PowerPivot success story. A bank institution has approached Prologika to help them implement a solution to report the customer’s credit history so the bank can evaluate the risk for granting the customer a loan. Their high-level initial requirements call for:

  • Flexible searching and filtering to the let the bank user find a particular customer or search for the customer accounts both owned by the bank or externally reported from other banks.
  • Flexible report layout that will let the bank user change the report layout by adding or removing fields.
  • Ability to download the report locally to allow the bank user to run the report when there is no connectivity.
  • Refreshing the credit history on a schedule.

Initially, the bank was gravitating toward a home-grown solution that would require tons of custom code to implement a desktop client that uses a third-party control or ReportViewer for reporting. One cool thing about Microsoft BI and my favorite tip I share at any public event is that reduces or eliminates custom code. This may sound strange coming from someone who spent most of his career writing code but less code is indeed in the customer’s best interest. So, we suggested a solution based on PowerPivot with the following implementation highlights:

  • Implement a PowerPiivot application and import the customer and account information.
  • Allow the end user to use slicers and PivotTable filters to search and filter data.
  • Upload to PowerPivot application to SharePoint to let each bank user view and interact with the report without requiring Excel 2010 installed locally.
  • Allow the user to download the application for offline reporting. This requires Excel 2010 with PowerPivot installed locally.

Here is a screenshot of the PowerPivot application rendered in SharePoint (sensitive information replaced to protect the innocent) that demonstrates some of the filtering capabilities that are built in Excel Services:

011511_1805_PowerPivotS1

Needless to say, this PowerPivot-based solution saved our customer a lot of time and money. True, we had to make some tradeoffs in terms of functionality and flexibility. One interesting implementation detail was that the reporting requirements required detail-level reporting at the account level instead of aggregating data. Out initial thought was that the in-memory VertiPaq database could handle this very efficiently. However, we ran into performance issues related to the fact that Excel generates MDX queries against VertiPaq and MDX is not optimized for detailed-level reporting especially when cross-joining multiple dimensions. To mitigate this issue, we took advantage of the Analysis Services Autoexists behavior that cross-joins attributes of the same dimension very efficiently. To do so, we consolidated the Customer and Account information into the Accounts table. We used the Customer table only for the Excel slicers and filtering by customer.

Looking ahead, it will be nice if a future release of PowerPivot detects that it deals with VertiPaq and generates native DAX queries instead of MDX to take a full advantage of VertiPaq. Crescent will be the first and only Microsoft client when SQL 11 ships that will generate native queries so detailed-level reporting will likely to be faster with Crescent.

PowerPivot Time Calculations

/0 Comments/in /by

A recommended practice for implementing time calculations in PowerPivot is to have a Data table with a datetime column. Kasper de Jonge explains in more details in this blog. This approach will probably save effort when importing data from normalized schemas and won’t require specifying additional arguments to the PowerPivot time functions. However, it will undoubtedly present an issue when importing data from a star schema. A dimensional modeling best practice is to have an integer key for a Data dimension table in the format YYYYMMDD and integer foreign keys in the fact tables. Luckily, you don’t have to normalize data back to datetime when building a PowerPivot model on top of star schemas after the issue with the All filter Kasper reported a while back got fixed in PowerPivot RTM.

Let’s consider the AdventureWorksDW schema. Its DimDate table has an integer key (DateKey). Let’s say you import this table in PowerPivot and name it Date. This is what the resulting PowerPivot table may look like. Fact tables join the Data table on the DateKey field which is an integer key.

011511_1555_PowerPivotT1

Now you want to calculate YTD values. Since we don’t have scope assignments in PowerPivot, you need to implement a time calculation for each field that you need YTD. For the SalesAmount field, you can use the following DAX expression:

=TotalYTD(Sum([SalesAmount]), ‘Date'[Date], All(‘Date’))

The TotalYTD function is one of the PowerPivot time intelligence functions. The second argument references the Date column in the Date table which must be of datetime. Finally, the third argument overwrites the evaluation context by telling PowerPivot to evaluate the expression irrespective of date selection, that is across all dates. A nice enhancement for a future PowerPivot (aka BISM) release is to provide some sort of UI to allow the modeler to flag which column in the Data table is a datetime column in order to avoid the All flag.

Book Review – Microsoft PowerPivot for Excel 2010

/0 Comments/in /by

0020.pp.png-550x0I dare to predict that in a few years after SQL 11 ships, there will be two kinds of BI professionals – those who know the Business Intelligence Semantic Model and those who will learn it soon. By the way, the same applies to SharePoint. What can you do to start on the path and prepare while waiting for BISM? Learn PowerPivot, of course, which is one of the three technologies that are powered by VertiPaq – the new column-oriented in-memory store. This is where the book PowerPivot for Excel 2010 can help. It’s written by Marco Russo and Alberto Ferrari, whose names should be familiar for those of you who have been following Microsoft BI for a while. Both authors are respected experts who have contributed a lot to the community. Stationed in Italy, they run the SQLBI website and share their knowledge via their blog and publications.

This is the second book that I’ve read about PowerPivot – after Professional Microsoft PowerPivot for Excel and SharePoint, which I reviewed in this blog. What I liked about this book is its deep coverage of Data Analysis Expressions (DAX). I specifically enjoyed the following chapters:

Chapter 6: Evaluation Context and CALCULATE – Provides a deep coverage of how DAX measures work. Although DAX is meant to be simpler than MDX, expressions can get complex and this chapter will help you understand how DAX works behind the hood.

Chapter 7: Date Calculations in DAX – Time calculations, such as YTD, QTD, are an important requirement for most BI projects. This chapter goes into details to explain how to implement them and provide workarounds for PowerPivot limitations.

Chapter 9: PowerPivot DAX Patterns – If you wonder whether PowerPivot can do this and that, read this chapter. It demonstrates advanced concepts ranging from ratio, percent of total, standard deviation, ranking over measures, Pareto computations, and more.

Chapter 10: PowerPivot Data Model Patterns – Another gem for addressing popular BI needs, such as banding, courier simulation, and many-to-many relationships.

Although not big in size (370 pages), you will find this book rich in patterns and solutions. What impressed me is that the authors put a great effort to cover not only the PowerPivot basics but to leave no stone unturned when the tool lacks in features. The authors discuss a requirement, approach it from different angles, and provide several implementation approaches. Thus, this book will benefit both beginners and advanced users. An indispensible resource for learning PowerPivot and giving a head start on BISM!

Crescent on the Horizon

/0 Comments/in /by

Now that the official word got out during the Ted Kummert’s keynote today at SQL PASS, I can open my mouth about Crescent – the code name of an ad-hoc reporting layer that will be released in the next version of SQL Server – Denali. Crescent is a major enhancement to Reporting Services and Microsoft Self-Service BI strategy. Up to now, SSRS didn’t have a web-based report designer. Denali will change that by adding a brand new report authoring tool that will be powered by Silverlight. So, this will be the fifth report designer after BIDS, Report Builder 1.0 (not sure if RB 1.0 will survive SQL 11), Report Builder 3.0, Visual Studio Report Designer.

Besides brining report authoring to the web, what’s interesting about Crescent is that it will redefine the report authoring experience and even what a report is. Traditionally, Reporting Services reports (as well as reports from other vendors) have been “canned”, that is, once you publish the report, its layout becomes fixed. True, you could implement interactive features to jazz up the report a bit but changes to the original design, such as adding new columns or switching from a tabular layout to a crosstab layout, requires opening the report in a report designer, making the changes, and viewing/republishing the report. As you would recall, each of the previous report designers would have separate design and preview modes.

Crescent will change all of this and it will make the reporting experience more interactive and similar to Excel PivotTable and tools from other vendors, such as Tableau. Those of you who saw the keynote today got a sneak preview of Crescent and its capabilities. You saw how the end user can quickly create an interactive report by dragging metadata, a-la Microsoft Excel, and then with a few mouse clicks change the report layout without switching to design mode. In fact, Crescent doesn’t have a formal design mode.

How will this magic happen? As it turns out, Crescent will be powered by a new ad-hoc model called Business Intelligence Semantic Model (BISM) that probably will be a fusion between SMDL (think Report Builder models) and PowerPivot, with the latter now supporting also relational data sources. The Amir’s demo showed an impressive response time when querying billion rows from a relational database. I still need to wrap my head around the new model as more details become available (stay tuned) but I am excited about it and the new BI scenarios it will make possible besides traditional standard reporting. It’s great to see the Reporting Services and Analysis Services teams working together and I am sure good things will happen to those who wait. Following the trend toward SharePoint as a BI hub, Crescent unfurtantely will be available only in SharePoint mode. At this point, we don’t know what Reporting Services and RDL features it will support but one can expect tradeoffs given its first release, brand new architecture and self-service BI focus.

So, Crescent is a code name for a new web-based fusion between SSRS and BISM (to be more accurate Analysis Services in VertiPaq mode). I won’t be surprised if its official name will be PowerReport. Now that I picked your interest, where is Crescent? Crescent is not included in CTP1. More than likely, it will be in the next CTP which is expected around January timeframe.

Applied PowerPivot Course Available

/0 Comments/in /by

082310_0058_PowerPivotT1I am excited to announce that Prologika has added an Applied PowerPivot course to the list of our training offerings in response to the strong interest for self-service BI. The class can be delivered as two-day online class (4 hours each day) or as one full-day onsite class. The cost for the online version is $599. Applied PowerPivot is designed to help students become proficient with PowerPivot and acquire the necessary skills to implement PowerPivot applications, perform data analysis, and share these applications with other users. The full course syllabus is available here. I scheduled the first run for September 21st. Happy self-service BI!

PowerPivot Implicit Data Conversion

/0 Comments/in /by

A student noticed an interesting behavior while I was teaching my PowerPivot class last week. He noticed that PowerPivot lets you join a text-based column to a numeric column. As it turns out, PowerPivot does an implicit conversion to text when it discovers that you are attempting to join columns of different data types. However, as a best practice, you should convert numeric columns to a numeric data type, such as Whole Number. This will help PowerPivot minimize storage and improve performance.