SQLBI - Marco Russo

Memory Settings in #Tabular #ssas (and Prepare for #msteched!)

2012-02-06T21:57:07Z

We are going very close to the release of SQL Server 2012 and after using Analysis Services 2012 for almost one year in a relatively small number of scenarios, I’m really curios to see the questions and the issues that will emerge when its adoption will go mainstream. I expect that many will consider a Tabular instance of Analysis Services similar to a Multidimensional one, also from a system engineer perspective, whereas there are many important differences. For example, the hardware required to...(read more)

Optimize Table and Column Size in #PowerPivot and #BISM #Tabular #SSAS #VertiPaq

2012-01-26T20:54:36Z

When you create a PowerPivot workbook or a BISM Tabular model (new in Analysis Services 2012), you store data by using the VertiPaq engine, which is an in-memory columnar database. Instead of considering the row of a table as the main unit of storage, it considers every column as a separate entity and stores data for every column in a separate way. This makes it very fast to query data for a single column, but requires a higher computational effort in order to retrieve data for several columns of...(read more)

Free online session at PASS DW/BI Virtual Chapter #sqlpass – Wed Jan 18th, 2012

2012-01-16T22:33:41Z

On January 18th, 2012 at 12pm Eastern Time (which is 5pm GM) Alberto Ferrari will deliver a free online session for the PASS DW/BI Virtual Chapter . This is the official description of the session. Many-to-Many Relationships in DAX The introduction of many to many dimension relationships in SSAS (since 2005) opened new scenarios that can be solved using OLAP cubes. Now, with the introduction of the Vertipaq engine and the DAX programming language, it seems that many-to-many relationships are no longer...(read more)

Using SQL to query Multidimensional and Tabular models #dax #sql #mdx #ssas

2012-01-12T13:12:00Z

Analysis Services answers to query in MDX, in DAX (by now just for Tabular models) and has a limited capability to answer SQL queries. It is not useful for any development or client tool, but I wanted to write a blog post on it in order to be able to retrieve these information I gathered during study of DAX and MDX queries sent to Tabular models. If you create an OLE DB connection using the MSOLAP driver you can send queries to BISM models according to the following schema: Multidimensional (aka...(read more)

Fact Tables with Different Granularities in #PowerPivot and #BISM #Tabular

2012-01-11T12:35:00Z

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 stored at product and day level. This scenario can be handled in a BISM Multidimensional model by using relationship with other attributes in a dimension,...(read more)

Pre-Conference Seminar at #TechEdEurope: #ssas #bism #tabular

2012-01-10T09:30:00Z

The Microsoft TechEd Europe 2012 will be in Amsterdam this year on June 26-29. The day before (25 June 2012) there are several pre-conference seminars and I’m glad to announce that I and Alberto Ferrari will deliver this one: Using BISM Tabular in Microsoft SQL Server Analysis Services 2012 […] After an initial introduction of the overall architecture with the Microsoft platform, including SharePoint, PowerPivot and Power View, most of the time is dedicated to the definition of a BISM Tabular model....(read more)

Free online session at PASS DW/BI Virtual Chapter #sqlpass – Tues Jan 10th, 2012

2012-01-09T13:39:00Z

Tomorrow January 10th, 2012 at 3pm Eastern Time (which is 8pm GMT) I will deliver a free online session for the PASS DW/BI Virtual Chapter . This is the official description of the session. Vertipaq vs OLAP: Change Your Data Modeling Approach While in classical OLAP cubes the clear winner data modeling technique is dimensional modeling, it is interesting to understand what will happen with the new Vertipaq engine. In Vertipaq there is no real need to do dimensional modeling, because the engine is...(read more)

Use parameters in your #DAX queries

2012-01-05T14:47:00Z

The DAX query syntax allows you to insert a parameter by using the @ symbol. EVALUATE CALCULATETABLE ( 'Product Subcategory' , 'Product Category' [Product Category Name] = @Category ) Depending on the client and on the driver you are using to execute your DAX query, this syntax may be supported or not. Here is a short recap: XMLA Execute Command: supported OLE DB MSOLAP driver with ADO connection: supported OLE DB MSOLAP driver with ADO.NET connection: not supported...(read more)

Analyze #DAX query plan with Profiler

2012-01-01T07:17:40Z

Jeffrey Wang started the 2012 (or finished the 2011, depending on your time zone) by publishing the first article dedicated to DAX Query plan . While I look forward to reading next articles, it is interesting the explanation of what is the relationship between MDX and DAX in Analysis Services 2012. An MDX query sent to a Tabular model is not converted in an equivalent DAX query, but at a certain point a DAX query plan is created (and probably many of them, both logical and physical plans). This is...(read more)

String Comparison in #DAX

2011-12-30T09:57:00Z

In DAX you don’t have the LIKE operator and you have to use SEARCH instead. However, performance are not very good and it is better to use LEFT and RIGHT if you just need to compare the initial (or ending) match of a string. I just wrote an article about string comparison in DAX . During my exploration of LIKE replacement functions in DAX, I have found that documentation of SEARCH for PowerPivot v1 is wrong – in DAX the SEARCH function is always case-insensitive, whereas FIND is always case-sensitive....(read more)

Query Projection in #DAX

2011-12-29T12:50:00Z

I wrote a short article about query projection in DAX. To make the story short, you should always use SUMMARIZE in order to remove columns from a table in a query. Thus, if you just want three columns from a table, instead of writing this in SQL: SELECT Col1, Col2, Col3 FROM Table you should write this in DAX: EVALUATE SUMMARIZE( Table, Table[Col1], Table[Col2], Table[Col3] ) A longer explanation is available in this article on SQLBI , that also explain how to rename a column, even if there are penalty...(read more)

Ratio Calculation Differences between #MDX and #DAX in #BISM #Tabular

2011-12-27T13:38:00Z

You should have already read that DAX is simpler than MDX. It’s true, and sometime you have to pay a price for simplicity. I am used to say that DAX is somewhere between SQL and MDX. In fact, SQL requires you to put any relationship in the query (using JOIN or subqueries) whereas MDX can only leverage on existing relationships in the underlying data model, without any syntax that allows to create relationships “on the fly” during a query. DAX is in the middle because it can leverage on existing relationships...(read more)

Best session in BI Platform Track at PASS Summit 2011 #sqlpass

2011-12-19T14:13:00Z

Last week the final session evaluations from PASS Summit 2011 have been communicated to speakers and best scores have been made public. My session was not particularly good: it had very good feedback (overall average 4.32) and some average one, mostly because the title (Vertipaq vs OLAP: Change Your Data Modeling Approach) suggested to someone a comparison about MOLAP and Vertipaq engines, whereas I dedicated the session to differences in data modeling for those two models. I know, I have to compare...(read more)

Vote desired sessions at #SQLBits 10

2011-12-12T20:53:00Z

Just a breaking news: you can vote the sessions you'd like to attend at the next SQLbits in London! I already did my job and I have to say that it's very hard to use your 10 sesssions - there are too many sessions that I'd like to watch!...(read more)

Sorting Dates Columns in #PowerPivot and #BISM #Tabular

2011-12-08T11:15:38Z

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 columns and not just once. For example, a MonthYear column containing January 2012 has to be sorted by Year and then by Month. In a Multidimensional model...(read more)