SQLBI - Marco Russo : Excel

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

Marco Russo (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

Marco Russo (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

Marco Russo (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.

New PowerPivot 2013 book available! #excel #powerpivot

Marco Russo (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!

New PowerPivot Workshop for Excel 2013 available! #powerpivot #excel2013 #excel

Marco Russo (SQLBI) — Mon, 04 Feb 2013 12:23:00 GMT

The Microsoft has launched the new Office 2013, we are proud to announce the availability of the new PowerPivot Workshop for Excel 2013! We have a fresh new website that offers all the training options: online and classroom courses. The target of this training are Excel Advanced Users and there are two versions of the workshop: the BASIC version is 2-day long and is good for everyone who want to start his experience with PowerPivot; the FULL version is 3-day long and the additional day is dedicated to more DAX content and practice, so also the more advanced users can satisfy his needs. All these versions are based on our upcoming Microsoft Excel 2013: Building Data Models with PowerPivot book that will be available in March 2013 (workshop’s attendees receive a free copy of that book).

The PowerPivot Workshop online is delivered every other month by me or Alberto and has different time scheduling depending on the delivery dates. We provide homework to students and use the available time online to teach content and answer attendees’ questions. The first online course will be delivered on February 19-21, 2013, and you have just one week to register using the use the Early Bird discount – more info for Basic and Full version following links.

We established partnerships with several authorized training centers around the world to deliver PowerPivot Workshop in classroom: in this case hands-on-labs assisted by the teacher are part of the course. Moreover, the PowerPivot course can be delivered in local languages: we report date, city, state, country and language of the Workshop in the list of courses available on our web site. We will publish more dates and locations in the coming weeks. Please note we are looking for training centers in states and countries we still don’t cover – we already have many ongoing discussions, but if you are interested just write us to get more info.

We'll also have more news for PowerPivot users in the coming weeks and months, which will be useful also to SSAS Tabular BI developers. The 2013 will be a long year!

Execute a #DAX Query on #SSAS #Tabular in #Excel

Marco Russo (SQLBI) — Thu, 31 Jan 2013 12:54:00 GMT

Apparently Excel does not offer a way to import data in Excel by using a DAX query on Analysis Services. The Data Connection Wizard seems to offers only the ability to create a PivotTable when you connect to Tabular, but not a Table (see the Table option disabled in the next picture).

However, the workaround is to create a connection file and changing it with an editor (such as Notepad) so that you can write your own DAX query (or MDX, too!) and import data in an Excel table.

You can write a step-by-step guide on the article Import Data from Tabular Model in Excel Using a DAX Query I published on SQLBI web site.

Linked Table, Reverse Linked Table and Linkback Table in Excel 2013 #powerpivot

Marco Russo (SQLBI) — Mon, 21 Jan 2013 13:35:00 GMT

Kasper de Jonge wrote a blog post last year introducing an interesting new feature in Excel 2013: you can write a DAX query that extracts data from the PowerPivot model and returns a table in Excel. Such a table can be used as a Linked table for the same PowerPivot model, creating an interesting opportunity to inject data in a data model mixing existing data and calculations obtained with Excel formulas.

I wrote an article on SQLBI that describes this feature in more detail and we discuss this capability also in the book Excel 2013 Building Data Models with PowerPivot that will be available in March 2013 (you can already order it). Because data can flow back and forth between Excel tables and the PowerPivot data model, we created a particular definition for each type of “connected” table:

Linked Table (Excel –> PowerPivot): the classical Linked Table
Reverse Linked Table (PowerPivot –> Excel): this is an Excel Table contained data resulting from a DAX query to the data model.
Linkback Table (PowerPivot –> Excel –> PowerPivot): this is a Linked Table based on a Reverse Linked Table (usually integrating some new columns filled with constants and/or Excel formulas).

The article shows how to create a Linkback table step-by-step, applying different Excel calculations to different rows (something you cannot do on calculated columns in DAX), and it also contains some consideration about refresh order that is applied to Linkback tables.

PowerPivot Compatibility across versions

Marco Russo (SQLBI) — Mon, 14 Jan 2013 12:44:00 GMT

There are several versions of PowerPivot available and starting with Excel 2013 there are also several versions of Excel. It is useful to look at the compatibility between the different versions of Excel and PowerPivot available now.

As a general rule when you have a PowerPivot workbook saved with a specific version of PowerPivot:

You can upgrade a workbook to a newer version of PowerPivot
You can upgrade a workbook to a newer version of Excel
You cannot open a workbook using a previous version of PowerPivot
You cannot open a workbook using a previous version of Excel, if it contains PowerPivot data

First caveat: you can open an Excel 2010 workbook containing a PowerPivot data model in Excel 2013, but once you save it in Excel 2013, you can no longer open it in Excel 2010. This is because a different file format for PowerPivot data used by Excel 2013 that cannot be understood by Excel 2010.

Second caveat: A file saved in Excel 2010 with PowerPivot 2012 (RTM or SP1) cannot be opened by an Excel 2010 running PowerPivot 2008 R2.

Each workbook has a compatibility level for PowerPivot data that corresponds to the PowerPivot version used to save the data. Here is the list of currently available compatibility levels:

1050 (2008 R2)
1100 (2012 RTM/SP1)
1103 (Excel 2013)

The following table tells you what happens when you try to open an Excel 2010 file in a certain compatibility level (columns) with a certain version of PowerPivot (rows):

	1050	1100
2008 R2	Read/Modify	Not Supported
2012 RTM	Read/Upgrade++	Read/Modify
2012 SP1	Read/Upgrade++	Read/Modify/Upgrade

++ It is important to call out that it is *not* supported editing 1050 PowerPivot models in the 2012 release – you *have to* upgrade the model to 110x before you can edit/refresh the model.

A detailed list of errors you can have opening different a workbook with a different compatibility level than the current version of PowerPivot you have is available in the article Version compatibility between PowerPivot Data Models in Excel 2010 and Excel 2013. As the article mention, PowerPivot for SharePoint can open PowerPivot workbooks of previous compatibility levels, but in order to upgrade the data on the server (scheduling a data refresh) you need to upgrade the workbook to the newer format.

You have to be careful using PowerPivot in mixed environment, especially when you want to share an Excel file containing PowerPivot data. Once you refresh PowerPivot data, you need to upgrade the compatibility level if you opened the workbook with a newer version of PowerPivot, and once you do that, your colleague with a previous version of PowerPivot can no longer open the same workbook. Saving the file on SharePoint makes it possible to navigate into data, but you need a correspondent or newer version of PowerPivot on SharePoint than that used to save the file.

For these reasons, when we provide the examples for our PowerPivot Workshop and our book for Excel 2010, the sample files are saved with the older version of PowerPivot (2008 R2). Now that we are working on the Excel 2013 version of the book and of the workshop (soon to be available!), we are finally going to release samples using a newer version of Excel and PowerPivot.

DATE function does not support all the dates in DAX by design #powerpivot #tabular #dax

Marco Russo (SQLBI) — Mon, 05 Nov 2012 12:52:00 GMT

The DATE function in DAX has this simple syntax:

DATE( <year>, <month>, <day> )

If you are like me, you never read the BOL notes that says in a clear way that it supports dates beginning with March 1, 1900. In fact, I was wrongly assuming that it would have supported any date that can be represented in a Date data type in Data Models, so all the dates beginning with January 1, 1900. The funny thing is that in some of the BOL documentation you will find that Date data type supports dates after March 1, 1900 (which seems not including that date, but this is a detail…). But we should not digress. The real issue is that if you try to call the DATE function passing values between January 1 and February 28, 1900, you will see a different day as a result.

evaluate row ( "x", DATE( 1900, 1, 1 ) )

-- return WRONG result
-- [x] 12/31/1899 12:00:00 AM

evaluate row ( "x", DATE( 1901, 2, 29 ) )

-- return WRONG result
-- [x] 2/28/1900 12:00:00 AM

evaluate row ( "x", DATE( 1900, 3, 1 ) )

-- return CORRECT result
-- [x] 3/1/1900 12:00:00 AM

As usual, this is not a bug. It is “by design”. The DATE function works in this way in Excel. And also in Excel it was “by design”. In this case the design is having the same bug of Lotus 1-2-3 that handled 1900 a leap year, even though it isn’t. The first release of Lotus 1-2-3 is dated 1983. I hope many of my readers are younger than that. I tried to open a bug in Connect. Please vote it. I would like if Microsoft changed this type of items from “by design” (as we can expect) to “by genetic disease”. Or by “historical respect”, in order to be more politically correct.

CUBEMEMBER and CUBEVALUE stop working after #PowerPivot upgrade to #Excel 2013

Marco Russo (SQLBI) — Wed, 31 Oct 2012 08:38:00 GMT

I found an issue upgrading an Excel workbook containing PowerPivot data from Excel 2010 to Excel 2013. All CUBEMEMBER and CUBEVALUE functions point to a cube name that has been changed between the two version – you have to no longer reference the PowerPivot Data name, replacing it with ThisWorkbookDataModel instead.

I wrote an article describing the change that you have to manually make to these Excel formulas in this article on SQLBI web site.

Community Events in Köln (October) and Copenhagen November #ssas #tabular #powerpivot

Marco Russo (SQLBI) — Wed, 12 Sep 2012 10:12:00 GMT

Short update about community events in Europe where I will speak.

On October 11 I will present DAX in Action in Köln - all details in the PASS local chapter here: http://www.sqlpass.de/Regionen/Deutschland/K%C3%B6lnBonnD%C3%BCsseldorf.aspx

I will be speaking at a community event in Copenhagen on November 21, 2012. The session will be Excel 2013 PowerPivot in Action and details about time and location are available here: http://msbip.dk/events/30/msbip-mode-nr-9/

I will be in Köln and Copenhagen to teach the SSAS Tabular Workshop. The workshop in Köln is the first in Germany and I look forward to meet new BI developers there.

Copenhagen is the second edition after another we delivered this spring. It is a convenient location also for people coming from Malmoe and Göteborg in Sweden. Last event in Copenhagen were conflicting with a large event in Sweden, maybe this time I'll meet more people coming from the other side of the Øresund Bridge!

Many other dates and location are available on the SSAS Tabular Workshop website.

Running Excel 2013 in a separate instance #excel #powerpivot

Marco Russo (SQLBI) — Tue, 24 Jul 2012 16:04:39 GMT

The new Excel 2013 preview is publicly available and it introduces many new features, like an integrated version of PowerPivot and Power View. These two add-ins are automatically installed but not enabled by default. You just have to go in Excel Options / Add-Ins and choose COM Add-ins from the Manage combo box, then press the Go… button; at this point, you can enable both PowerPivot and Power View add-ins. There are many other posts in the blogosphere that describe the feature and the capabilities of these two tools, and I will cover some deeper consideration about specific new features in the future. In this post, I just want to highlight a new behavior of Excel that is not directly related to these add-ins, but still may affect their behavior.

One important new feature of Excel 2013 is that it no longer supports MDI (Multiple Document Interface) and it now uses the SDI (Single Document Interface) paradigm, just following a trend that other Office applications, like Word, started a few years ago. With this new behavior, every Excel document opens a window handled by the same Excel process. This is not different from previous versions of Excel and corresponds to the behavior you experienced whenever you loaded more documents within the same Excel process. However, until Excel 2010 it was very easy to create a separate Excel process so that it was possible to manipulate a document during a complex calculation in another workbook. Fundamentally, every time you opened a new Excel window, you always obtained a new Excel process and the same Excel process would have been used only by opening an existing document when another Excel document were already opened.

Usually having multiple documents within the same process can save several resources (RAM in particular). However, this approach can also block user interaction whenever a long and complex calculation is requested to Excel. In order to solve this issue, you may decide to explicitly open a new Excel instance, so that a long running operation on one Excel document does not block user interaction with other documents.

With Excel 2013, the default you have is to create a new window within the existing Excel process. In order to force the creation of a separate instance of the Excel process, you have these options:

From the command prompt, run EXCEL /X and you will open Excel window as a new instance. The /X command switch forces the creation of a new instance.
Right click on the Excel Tab in Windows taskbar keeping the ALT key pressed. And without releasing the ALT key, click on “Microsoft Excel 2013” option from the context menu. Excel will ask you if you require opening Excel as a new instance. You might press YES.

Please not you cannot use this technique to open an existing document – you have to create the new Excel process first, and then open the existing workbook from there

Knowing this technique can be particularly important if you are a PowerPivot user, especially if you use a 32-bit version of Excel. All PowerPivot data of different workbooks are loaded within the same virtual address space, which is limited to 2 or 3Gb in a 32-bit application. If you want to avoid being short on RAM with complex models and you want to be able to isolate a long running calculation without blocking you from using Excel with another workbook, knowing how to create a separate Excel instance is an important skill you need in Excel 2013.

Workaround for lack of PowerPivot API in Excel

Marco Russo (SQLBI) — Mon, 08 Aug 2011 14:22:45 GMT

A few days ago a post about VSTO-based PowerPivot Workbook in the PowerPivot Team Blog shown a workaround for the lack of PowerPivot API in Excel. In reality, I don’t like this workaround at all, because it relies on displaying message boxes to the end...(read more)

Some more comments about XLCubed

Marco Russo (SQLBI) — Mon, 09 May 2011 07:05:00 GMT

Some weeks after my blog post about Bonavista Dimensions I’ve been able to look at a comprehensive demo of XLCubed , but since then I’ve been too busy to write about that (and in general to blog – I have several topics I will blog about as soon as the...(read more)

Parameterize Charts using Excel Slicers in PowerPivot

Marco Russo (SQLBI) — Wed, 02 Feb 2011 23:18:00 GMT

One new nice feature of Excel 2010 is the Slicer. Usually, slicers are used to filter data in a PivotTable. But they might be also useful to parameterize an algorithm or a chart! We discussed this technique in our book , but Alberto Ferrari wrote a post...(read more)