A few weeks ago I received a question that inspired me in writing this article about how to handle data at different granularities in PowerPivot and BISM Tabular. I think this is a common pattern when you have a budget table that contains data at an aggregated level (like month and product category) and you want to compare it with sales that are ...

I just published an article about Sorting Dates Columns in PowerPivot v2 and BISM Tabular on SQLBI website. In reality, the article is about sorting columns by other columns in a Tabular model, but the common scenario in which you need that is in a Calendar table where you can have columns that have to be sorted by using a combination of other ...

I just published the article Converting MDX to DAX – First Steps on the renewed SQLBI web site about converting MDX to DAX. The reason is that with BISM Tabular in Analysis Services 2012 you will be able to write queries in both DAX and MDX. If you already know MDX, you might wonder how to “translate” your MDX knowledge in DAX. I think that this ...

If you are looking to create many-to-many relationships based models in PowerPivot and in BISM Tabular, probably one day you will be interested in using them with Power View (formerly codename “Crescent”). There is a very bad news for you: it appears that, at least in its first release, Power View will not support this scenario, showing a behavior ...

As I already announced on this blog some weeks ago, there will be a PowerPivot Workshop in Lisbon (Portugal) on September 19-20, 2011. The evening of September 19 there will be also a free community event in Lisbon where Alberto will speak: you can find all details and registration link here. We know that August is an holiday month for many ...

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) but to do that it is often useful to have two independent set of attributes: one for the ...

Alberto Ferrari published an interesting blog post regarding how to write particular calculations over a parent-child hierarchy in PowerPivot Denali (and in BISM Tabular, of course). One case is the Sum of Leaves (summing only the leaves of a parent-child hierarchy) and the other is the Sum of Children (computes all the nodes that are descendants ...

Microsoft released the CTP3 of Microsoft SQL Server Code Name “Denali”, as you can see in the SSAS product group blog post. This is the first public release of Denali and after many months of forced silence (I was under NDA) I can finally write my first hand experience with this new version! In the next months I will write on the blog more ...

Surfing on the web, here, I came into this intriguing question: How do we ask something like ''Show me how many customers have an iPad but don't have a book?'' We are speaking about a sales analysis where the canonical entities are Customers, Products and Sales. Moreover, because we have been used to speak about cycles since many years ago, ...

During TechEd 2011, Microsoft announced an important update to its BI roadmap. The reason why this is important is related to the previous announcement, which I discussed in November 2010 by including links to several sources and comments. At PASS Summit 2010 the announcement regarding the next version of SQL Server, codenamed ''Denali'', ...