Search results matching tag 'PowerPivot'

Group Sales by Age of Customers #dax #powerpivot #tabular

sqlbi — Mon, 06 May 2013 12:16:00 GMT

I published an article describing how to implement the grouping of sales transactions by age of customer at the moment of the transaction by using PowerPivot or Analysis Services. The same pattern can be used also for any kind of banding operation, this specific case is useful also to recycle the formula that gets the exact age of the customer for each transaction.

An interesting point is related to performance optimization. The technique is based on adding a calculated column in a table that might contain millions of transactions. This is less expensive than adding a column that contains a foreign key and then a relationship between a table containing group definitions and the transactions table. Every relationship is expensive and generates additional structures (you can see more files in the Analysis Services database, too). Adding one or two columns that have a low number of distinct values (10-15 rows) usually has a lower memory cost than creating a relationship with another table. The article also contains PowerPivot examples for both Excel 2010 and Excel 2013.

If only I could decouple attribute visualization from physical structure, I would put these “degenerate dimensions” in a separate folder, because in this way such attribute will be included in attributes belonging to the fact table, which might not be so clear in presenting data. However, I understand that such a decoupling could make live very hard to DAX clients (but probably for MDX it could be not a big issue).

DAX Studio for Excel 2013 finally available! #dax #excel #powerpivot #ssas #tabular

sqlbi — Sat, 04 May 2013 05:49:00 GMT

I'm so happy that DAX Studio finally supports Excel 2013! As Darren Gosbell described in his blog, this release has a few internal changes that will better support future enhancements. I will port the code to capture the query plan for a query in this new release, but unfortunately it will require some weeks because I'm traveling a lot in these days.

If you write DAX formulas and queries for PowerPivot or Analysis Services Tabular, DAX Studio is a must have tool: do you really want to live without a DAX Editor? There are a lot of possible improvements and I hope other contributors will give their help to this Codeplex project.

PowerPivot Workbook Size Optimizer #powerpivot #tabular

sqlbi — Tue, 30 Apr 2013 10:50:00 GMT

Microsoft released the Workbook Size Optimizer for Excel, the first version of an Excel add-in for Excel 2013 that inspects the data model and suggest possible optimizations. Fundamentally, it tries to apply the best practices descripted in a white paper I mentioned a few weeks ago, removing useless columns and changing granularity to those that could reduce the overall memory cost of a table.

There are different setup available in the download page, depending on operating system (Windows 7 or Windows 8) and on Office version (32 or 64 bit). Once installed, you have a new tab in the Excel ribbon, called Workbook Size Optimizer, showing a single button that starts a wizard.

I tried to run the optimizer with a workbook where I imported several tables from Adventure Works Data Warehouse sample database. The first page shows a few information about the workbook size and the option of automatic detection or manual choice of rules. The latter is an option you can request also later, so I started with the default.

After a short analysis, I received three smart suggestions (considered the model I have). We might wonder that removing UnitCost is a smart thing, because it could be required in order to perform calculations and rounding the value might be not correct for our analysis.

Since I requested to apply some changes, I have the option of changing which rules to apply. This corresponds to the choice you have if you choose “Let me choose the rules myself” in the first screen of the wizard.

I kept all the rules and after I click Next I had to wait several seconds in order to complete the optimization process. The result shows a few information about the result of the job.

This is a good starting point. Don’t blindly trust any suggestion and try to consider carefully the rules to apply in order to avoid losing important data for your analysis. Moreover, you might have a better knowledge of your data model than a wizard and consider the deletion of many useless columns (for your analysis) that are not identified by the wizard. My article Checklist for Memory Optimizations in PowerPivot and Tabular Models contains several best practices that you can apply to your data model.

LASTNONBLANK and FIRSTNONBLANK functions work with any column #dax #powerpivot #ssas #tabular

sqlbi — Fri, 19 Apr 2013 10:28:50 GMT

During a PowerPivot Workshop course we received an interesting question from a student: “Can I use LASTNONBLANK (and FIRSTNONBLANK) with a column which is not a date column?”

The reason is that we introduce LASTNONBLANK in the Advanced Time Intelligence module, because its typical use case is on a date column. However, you can use these functions on any column, which raises the question about what happens at that point. The sort order used is the one that depends on the data type of the column. If it is a Text column, the alphabetical sort order is the reference order. If it is a number, then the numeric order is the reference.

What happens if a column has the “Sort By Column” property set to another column? This sort order is *not considered* by LASTNONBLANK and FIRSTNONBLANK functions. Even if a PivotTable shows you data sorted according to Sort by Column property, any DAX formula ignores such a sort order. Thus, be careful writing your DAX queries if you have to do some assumptions on the sort order of a column using DAX functions that rely on sort order, such as LASTNONBLANK and FIRSTNONBLANK.

Optimize memory in #powerpivot and #ssas #tabular

sqlbi — Wed, 03 Apr 2013 10:55:00 GMT

Microsoft published an interesting article about how to optimize memory consumption of a PowerPivot Data Model in Excel 2013. All these suggestions are also valid for SSAS Tabular. I also wrote an article Checklist for Memory Optimizations in PowerPivot and Tabular Models with a summary of the best practices.

The short list of things to do is very valuable:

Removing columns non necessary for analysis

Identity column (PK) of a fact table
Timestamps, guid and other info useful for auditing and replication, but with no data for analysis

If a column has too many distinct value and cannot be removed (i.e. transaction ID in a fact table for drillthrough), consider splitting the column into multiple distinct parts.

Each one of the parts will have a small number of unique values, and the combined total will be smaller than the original unified column.
Always separate date and time in two columns, instead of the original datetime.
In many cases, you also need the distinct parts to use as slicers in your reports. When appropriate, you can create hierarchies from parts like Hours, Minutes, and Seconds.
Keep only the granularity you really need.

Normalize columns keeping only those with the lower number of distinct values

For example, if you have quantity, price and total line amount, import quantity and price and calculate total line amount as SUMX( Sales, Sales[quantity] * Sales[price] ) instead of SUM( Sales[line amount] ) importing line amount.

Reduce precision of number to reduce distinct values (i.e. round to integer if decimal values are not relevant).

The reason is that VertiPaq compress data at column level, creating a dictionary for each column and storing for each row only the number of bits required to store the index to the dictionary. More details in the article Optimizing High Cardinality Columns in VertiPaq I wrote a few months ago and on the SSAS 2012 Tabular book.

A useful macro to analyze memory consumption and quickly identify the most expensive tables and columns in a PowerPivot workbook is available on Kasper De Jonge blog What is eating up my memory the PowerPivot / Excel edition. There is also a version for a Tabular database in his What is using all that memory on my Analysis server instance post.

New PowerPivot 2013 book available! #excel #powerpivot

sqlbi — Tue, 02 Apr 2013 11:25:00 GMT

Our new book about PowerPivot 2013 is finally available in printed edition, too!

The title is Microsoft Excel 2013: Building Data Models with PowerPivot and it is a partial rewriting of the previous book about PowerPivot for Excel 2010. In the previous book we had a target audience that included advanced Excel users and BI developers, because at that time there was no option to get the same engine in Analysis Services. But 30 months are elapsed, a new version of Analysis Services has been released and in this new book we focused mainly on Excel users. For this reason, we wrote a comprehensive book of all the feature of PowerPivot, but most important we tried to pass concepts of data modeling that might be pretty obvious for a DBA and a BI developer, but are completely new to an Excel user that never had the ability to create a data model with more than one table.

This book is focused on Excel 2013, so we included specific feature of this release related to PowerPivot, such as writing DAX queries and linked back tables, and features unique to Excel 2013, such as Power View. However, all of the PowerPivot features (so the 85% of the book) are good also for PowerPivot for Excel 2010 in its latest release (SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010), so you can safely use this book for both version of Excel.

You can download the first chapter of the book from the book page on SQLBI web site. And if you want to attend a training in a classroom or online, look at the complete list of available trainings on PowerPivot Workshop web site. The next online courses are scheduled on April 22-24, 2013 and June 17-19, 2013 (following online workshops are every other month).

Here are the links to directly order the book on Amazon around the world:

Amazon.com: hardcopy – kindle
Amazon.ca: hardcopy – kindle
Amazon.co.uk: hardcopy – kindle
Amazon.de: hardcopy – kindle
Amazon.es: hardcopy – kindle
Amazon.fr: hardcopy – kindle
Amazon.it: hardcopy – kindle

And here is the list of chapters:

Chapter 1 Introduction to PowerPivot
Chapter 2 Using the unique features of PowerPivot
Chapter 3 Introducing DAX
Chapter 4 Understanding data models
Chapter 5 Publishing to SharePoint
Chapter 6 Loading data
Chapter 7 Understanding evaluation contexts
Chapter 8 Understanding CALCULATE
Chapter 9 Using Hierarchies
Chapter 10 Using Power View
Chapter 11 Shaping the Reports
Chapter 12 Performing Date Calculations in DAX
Chapter 13 Using Advanced DAX
Chapter 14 Using DAX as a Query Language
Chapter 15 Automating Operations Using VBA
Chapter 16 Comparing Excel and SQL Server Analysis Services

This book should help you starting with PowerPivot at the very beginning, and you will probably use only the first chapters at that point. Over time, you will use following chapters and will learn more advanced techniques. This is not a book you can digest in a couple of days (after all, it is 500 pages long!), it will be your companion for several months, until you will master PowerPivot!

Discount for PASS Business Analytics Conference 2013 #passbac #ssas #sqlpass

sqlbi — Tue, 12 Mar 2013 12:02:00 GMT

One month ago I wrote about my sessions at PASS Business Analytics Conference 2013, in Chicago, IL on April 10-12, 2013. If you still have not registered, you can save $200 by using the code BAC228BL and you should hurry up, because there is another discount if you register within March 15, 2013.

If you are too lazy to click on the previous post, I will speech in two sessions:

Modern Data Warehousing Strategy
Self-Service Data Modeling

And now that Data Explorer Preview has been made public I can disclose that Data Explorer will be covered in my Self-Service Data Modeling session! I thought about writing an article about Data Explorer, but there is already a good coverage and I suggest you to read these blogs:

Introduction to Data Explorer Preview for Excel by Jason Thomas
Several posts by Chris Webb
Announcement on Data Explorer Team blog

Microsoft BI Security White Paper #ssas #powerpivot #sharepoint #ssrs

sqlbi — Mon, 11 Mar 2013 11:47:00 GMT

There is a new whitepaper from Microsoft, Microsoft BI Authentication and Identity Delegation, which describes all the authentication and delegation scenarios with Microsoft BI technologies:

Personal BI Scenarios (Excel)
Team BI Scenarios (SharePoint)
Corporate BI Scenarios (Reporting Services, Analysis Services)
Federated BI Scenarios (Multi-Forest AD, Extranet, Cloud)

This is the new reference whitepaper to correctly plan and configure the security environment of a BI solution based on Microsoft BI stack.

Update to the PowerPivot for Excel 2013 licensing #powerpivot #excel

sqlbi — Wed, 27 Feb 2013 14:21:00 GMT

~~Rob Collie~~ Ken Puls (thanks Bob!) wrote a very interesting post about the PowerPivot for Excel 2013 licensing.

Short recap:

PowerPivot for Excel 2013 is only available in a few editions of Excel/Office (more details here)
If you want to buy a perpetual license of Excel with PowerPivot, you need to be included in a Volume Licensing program
The easiest way to enter a volume licensing program is buying 5 licenses for Microsoft products, even different ones
The cheapest license you can buy is around 7$ (i.e. Microsoft DVD Playback Pack for Windows Vista Business)
Buying 4 of them grants you the ability to buy Office 2013 Pro Plus license as fifth product to enter the volume license agreement

This really does not make any sense to me, and like everyone I hope Microsoft will fix the licensing issues that could limit the adoption of PowerPivot for Excel 2013. In the meantime, there is a way to get the product spending just 30$ more.

DAX Time Intelligence for 4-4-5 Calendar, ISO Calendar and other custom ones #dax #powerpivot

sqlbi — Tue, 19 Feb 2013 14:21:00 GMT

DAX offers a set of Time Intelligence functions that simplify writing DAX expressions such as YTD, YOY and other time-related calculations. However, these functions only works when some assumptions are valid: your periods should be “natural” months and quarter. Some industries, such as retail and manufacturing, are used to accounting periods that are based on weeks instead of months. One month and one quarter are a set of weeks and a week cannot be split in different months, quarter or years. Making DAX working on these custom calendars requires you to write some DAX expression without using the built-in Time Intelligence functions.

I wrote an article, Week-Based Time Intelligence in DAX, which describes how to write the common DAX calculations required on a custom calendar. I also included two samples, one for Excel 2010 and the other for Excel 2013, so that you can easily work on both versions (as you know, downgrading a workbook from Excel 2013 to Excel 2010 with PowerPivot is not possible).

An important tip you will find is that creating a column that contains the number of days elapsed in a year (or the running total of days in the year, if you prefer) makes it easy writing the FILTER required to use the right set of days in each calculation. After all, this technique is very similar to the one you would use in SQL to perform the same calculation, for this reason DAX is considered more intuitive than MDX by developers with a SQL background.