SQLBI - Marco Russo : SSAS, PowerPivot

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.

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.

Optimize memory in #powerpivot and #ssas #tabular

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

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

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

March 2013 Events and Workshops in Aarhus, DK and Utrecht, NL #ssas #powerpivot

Marco Russo (SQLBI) — Wed, 06 Feb 2013 09:22:00 GMT

Spring is coming and we published many dates of SSAS Tabular Workshops around Europe and online.

Alberto Ferrari is coming to Aarhus, Denmark, for a 2-day SSAS Tabular Workshop on March 12-13, 2013 (you can see more info and register here). And Alberto will also deliver a free session in a community event on March 12 evening: the session is PowerPivot for Excel 2013 in Action and you can find more info here.

In the same month, I will go to Utrecht, The Netherlands, for the same 2-day SSAS Tabular Workshop on March 25-26, 2013 (info and registration here). We are working on a free community event on March 25, I will write an update post as soon as we define program and schedule.

If you cannot travel, Alberto will also deliver a SSAS Tabular Workshop Online on February 27-28, 2013. The schedule is optimized for America’s time zones, so attending from Europe might be convenient if you are busy in daytime. You can register this week taking advantage of the discounted Early Bird price.

And there is more to come… stay tuned!

LASTDATE dates arguments and upcoming events #dax #tabular #powerpivot

Marco Russo (SQLBI) — Mon, 01 Oct 2012 17:04:00 GMT

Recently I had to write a DAX formula containing a LASTDATE within the logical condition of a FILTER: I found that its behavior was not the one I expected and I further investigated. At the end, I wrote my findings in this article on SQLBI, which can be applied to any Time Intelligence function with a <dates> argument.

The key point is that when you write

LASTDATE( table[column] )

in reality you obtain something like

LASTDATE( CALCULATETABLE( VALUES( table[column] ) ) )

which converts an existing row context into a filter context.

Thus, if you have something like

FILTER( table, table[column] = LASTDATE( table[column] )

the FILTER will return all the rows of table, whereas you probably want to use

FILTER( table, table[column] = LASTDATE( VALUES( table[column] ) ) )

so that the existing filter context before executing FILTER is used to get the result from VALUES( table[column] ), avoiding the automatic expansion that would include a CALCULATETABLE that would hide the existing filter context.

If after reading the article you want to get more insights, read the Jeffrey Wang's post here.

In these days I'm speaking at SQLRally Nordic 2012 in Copenhagen and I will be in Cologne (Germany) next week for a SSAS Tabular Workshop, whereas Alberto will teach the same workshop in Amsterdam one week later. Both workshops still have seats available and the Amsterdam's one is still in early bird discount until October 3rd!

Then, in November I expect to meet many blog readers at PASS Summit 2012 in Seattle and I hope to find the time to write other article on interesting things on Tabular and PowerPivot. Stay tuned!

The ultimate Calendar table for #powerpivot (and #ssas, why not)

Marco Russo (SQLBI) — Mon, 10 Oct 2011 12:09:00 GMT

Boyan Penev announced the availability of DateStream project on CodePlex: it is a data feed that can be consumed by PowerPivot but also to generate a calendar table for any type of usage. Yes, Analysis Services has a wizard, but when you are in a hurry...(read more)

Separate date and time in #powerpivot (and #bism tabular)

Marco Russo (SQLBI) — Thu, 01 Sep 2011 09:24:00 GMT

In PowerPivot you can import data from several sources and it is often the case that you import a DateTime column from a database. From an analytical point of view, you usually make analysis over dates (day/month/year/quarter/week) and over time (hour/minute)...(read more)

The Microsoft BI Roadmap: BISM, UDM and Beyond

Marco Russo (SQLBI) — Mon, 15 Nov 2010 13:05:00 GMT

Microsoft recently announced a new roadmap for its BI architecture. The next version of SQL Server, codenamed “Denali”, is going to introduce a new semantic model named BISM (Business Intelligence Semantic Model). Analysis Services will host it and it...(read more)