Search results matching tags 'Analysis Services', 'DAX', and 'Tabular'

Advanced DAX course in May - unique date in 1H 2013 #dax #tabular #ssas #powerpivot

sqlbi — Mon, 22 Apr 2013 08:55:00 GMT

One year after the release of SQL Server 2012 I see the growing demand for DAX. There are two reasons for that: an higher number of PowerPivot users started to build more complex data models, and SSAS Tabular is starting to be adopted by a larger number of companies, with and without a previous experience on former versions of Analysis Services.

For this reasons we decided to offer a first public edition of our Advanced DAX Workshop, a training on DAX that is aimed at Advanced PowerPivot users and Analysis Services developers that want to master the DAX language. Up to now, we offered this course only for private classes, because of the limited demand, but now there is enough interest and adoption to justify an open class.

The goal of this DAX training is learning to write DAX expressions for measures and calculated columns, DAX queries for reporting needs, read DAX query plans and optimize DAX formulas. The course is a three-day workshop that includes many hands-on lab sessions, with exercises that will guide you in the learning process of the more advanced DAX concepts, enabling you to master the writing of DAX code.

The course will be in London on May 13-15, 2013. There are direct flights with a huge number of countries and cities, also outside of Europe. We do not expect to deliver other editions of this course before other 5-6 months, so don’t lose the chance to attend this intensive DAX master course. I will be the teacher in this edition and Chris Webb will assist me in organization with Technitrain. So don’t wait, early bird discount will expire in a few days, register now and join us in London!

SQLBits XI in May 2013 – many reasons to attend! #sqlbits #dax #tabular

sqlbi — Wed, 20 Feb 2013 09:33:00 GMT

I and Alberto Ferrari will speak at SQLBits XI (Nottingham, UK – May 2-4, 2013) and there are many good reasons to join us there, especially if you are interested to DAX!

Here are our sessions:

Thursday, 02 May 2013 – Training Day - From 0 to DAX

The entire day is dedicated to learning DAX, starting from the syntax and going forward with more complex syntaxes for both expressions and queries in DAX.
Technical level: the goal is to be introductive, explaining the important concepts in DAX (such as filter context, row context, context transitions) and covering the usage of the most common DAX functions.
Target audience: Information Workers interested in exploiting the many interesting features of PowerPivot for Excel 2013 and BI developers who want to learn the DAX language.
In practice: if you started using DAX two years ago and created several projects in Tabular and/or PowerPivot with complex calculations, this is not the workshop for you (read about an Advanced DAX Workshop later in this post). But if you just started your first Tabular or PowerPivot projects and you are still wondering what CALCULATE is all about, then this is definitely the right training for you!

Friday, 03 May 2013 – 10:50am – Modern Data Warehousing Strategy

How to design a Data Warehouse in 2013? Should we change something considering the introduction of in-memory technologies such as xVelocity? Is Self-Service BI affecting the way we design and implement a Corporate Data Warehouse?
This is not a session about a specific feature of a product, but features and products have their effects on the data warehouse design. We’ll stop one hour trying to understand what changes in the big picture, affecting also our day-by-day job.

Friday, 03 May 2013 – 2:40pm – Inside xVelocity InMemory Engine (VertiPaq)

Do you use PowerPivot and/or Analysis Services Tabular? They use the same engine (xVelocity/VertiPaq) and you should know how it works.
This session will show how xVelocity/VertiPaq works, how it compresses and stores data and why it is so fast answering to your queries.
Why this is important? Because it is geeky! And because this knowledge will help you optimizing storage and DAX queries.

Saturday, 04 May 2013 – 10:50am – DAX Query Engine Internals

DAX is amazingly fast. But some query might be not fast enough.
Do you want to know how to optimize your DAX query? This session is for you.
Knowing the internals of xVelocity InMemory engine is just the first step (see Friday’s session). Then you need to apply this knowledge to DAX and understand why different DAX syntaxes for the same result might have different performance.
Understanding cross-filtering is a key to master DAX optimization. The more you know DAX, the more you will appreciate this session!

As I said before, the training day is an introductive course. If you already know DAX and want to improve your skills, there is a 3-day Advanced DAX Workshop I will teach in London on May 13-15, 2013. It could be also a good reason to visit London if you come from other countries!

Now that we are almost done with the PowerPivot for Excel 2013 book, we will write more articles – stay tuned!

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.

First DAX Advanced Workshop in London, May 2013 #dax #tabular #ssas

sqlbi — Thu, 07 Feb 2013 10:21:00 GMT

Are you working with SSAS Tabular? Are you an experienced PowerPivot user? In both cases, you should be aware that there is only one skill that is important for PowerPivot and SSAS Tabular, and it is the DAX language. I and Alberto have been using DAX since 2010, wrote several books containing several chapters about DAX and we know that there is still much to do. We have plans to publish more content online (more on this in a few months…) but we realized that the number of companies building tabular models is increasing every day. The common issues we see are about design, calculation, queries and performance. All of them are related to DAX, and we understand that learning DAX requires mentoring and practice (if only we had that 3 years ago…).

Well, the good news is that now you can learn DAX deeper and faster. We created a new intensive DAX course that we called DAX Advanced Workshop. It is a three-day classroom that is aimed to Advanced PowerPivot users and Analysis Services developers that want to master the DAX language and improve their skills in performance optimization. The course includes hands-on lab sessions assisted by the trainer (me or Alberto), including exercises for creating queries, solving business problems and locating performance bottlenecks in DAX.

Prerequisite: Attendees need to have a basic knowledge of the SQL 2012 Analysis Services Tabular modeling or they need to be familiar with PowerPivot for Excel and have produced at least some basic reports. A prerequisite of the course is the participation to a SSAS Tabular or PowerPivot Workshop, or having equivalent experience.

If you think you’re ready for that, we have a single date in Europe before summer, and it will be in London on May 13-15, 2013. You can download course outline and register here. Seats are limited, hands-on-labs requires real assistance. You have to bring your laptop for hands-on-labs. It will be funny, but it will be tough!

We don’t have plans for other editions until next fall, so if you are interested, free your agenda. Unless you want an on-site edition in another date, of course.

Please, let me know if you are interested in US. You might have a good excuse to visit London, but if this is not enough, then give me your feedback. We will evaluate demand from US in order to schedule other public classes.

End of 2012 and news in 2013 for #PowerPivot, #ssas #tabular and BI

sqlbi — Thu, 27 Dec 2012 18:26:38 GMT

This year is going to end, Maya failed their predictions and while this is bad for predictive industry, it’s also good for all of us!

We’ve seen many news in Microsoft BI stack:

Excel 2013 has been RTM’d – its General Availability is expected in early 2013 but many early adopters are already using it daily (myself included)
PowerPivot and Power View are both integrated in Excel 2013 and I think that they are the most compelling reason to upgrade your Excel. Power View alone worth the effort
SQL Server 2012 has been released and I’m seeing the first SSAS Tabular projects going in production in these days.
I, Alberto and Chris published the book Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model, which has good reviews and is selling well. It’s clear that the interest is high in this new technology for Corporate BI
We also run several SSAS Tabular Workshops, many sessions about Tabular topics in conferences and user groups.
I stopped writing blogs/article in the last weeks, but don’t worry, it’s just that I and Alberto are just writing another book…
We recorded several videos about DAX for Project Botticelli and we’ll enhance this library in 2013.

So what’s next in 2013? Let’s see some anticipations and personal predictions:

SharePoint 2013 and Office 365 are going to further innovate Self-Service BI. Maybe that this process is not so fast as Microsoft hopes, but it’s a clear direction and I hope that new features in this area will appear with a sooner release cycle, we simply cannot wait other 3 years to get more features on the cloud.
Mobile BI with Microsoft Technologies is going to be a real thing in 2013. And Office for iPad should be real, too. Power View for iPad as a native application is also another important step.
I will speak at PASS BA Conference 2013 in Chicago, IL (April 10-12, 2013) – at that time, I hope much of the MS Mobile BI tools will be available to anyone.
In March 2013 our new PowerPivot book Microsoft Excel 2013: Building Data Models with PowerPivot will be published by Microsoft Press. We are working hard these days in order to complete the drafts, we listened to all of the feedback we received, especially from Excel users. Time will tell if we did a good job on that.
We will publish other content on SQLBI web site and we have some interesting news for PowerPivot users and Tabular developers… but it’s too early to discuss that. Just stay tuned! Why not registering to our newsletter? (hint – scroll down to the end of the page to insert your email)
We will announce a new PowerPivot Workshop for Excel 2013 (we are working on an updated web site – it should be ready by the end of January)
In January we will announce new dates for our SSAS Tabular Workshop and the new DAX Advanced Workshop.
I hope DAXMD will be released this year, it is already available as CTP. If you create canned reports for SSAS Multidimensional, consider using DAX as a query language when DAXMD will be released.
DAX will be always more important…

Economy is not going well in many countries, and I’ve seen this impacting also our ecosystem. Sometimes you might think that your efforts are worthless, but in the long term the investments in updating skills produces a good return, at both company and individual level. Don’t give up!

I wish you a Great 2013!

Meet SQLBI at PASS Summit 2012 #sqlpass

sqlbi — Fri, 02 Nov 2012 12:11:00 GMT

Next week I and Alberto Ferrari will be in Seattle at PASS Summit 2012. You can meet us at our sessions, at a book signing and hopefully watching some other session during the conference. Here are our appointments:

Thursday, November 08, 2012, 10:15 AM - 11:45 AM – Alberto Ferrari – Room 606-607
Querying and Optimizing DAX (BIA-321-S)
Do you want to learn how to write DAX queries and how to optimize them? Don’t miss this session!
Thursday, November 08, 2012, 12:00 PM - 12:30 PM – Bookstore
Book signing event at the Bookstore corner with Alberto Ferrari, Marco Russo and Chris Webb
Visit the bookstore and sign your copy of our Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model book.
Thursday, November 08, 2012, 1:30 PM - 2:45 PM – Marco Russo – Room 611
Near Real-Time Analytics with xVelocity (without DirectQuery) (BIA-312)
What’s the latency you can tolerate for your data? Discover what is the limit in Tabular without using DirectQuery and learn how to optimize your data model and your queries for a near real-time analytical system. Not a trivial task, but more affordable than you might think.
Friday, November 09, 2012, 9:45 AM - 11:00 AM
Parent-Child Hierarchies in Tabular (BIA-301)
Multidimensional has a more advanced support for hierarchies than Tabular, but in reality you can do almost the same things by using data modeling, DAX functions and BIDS Helper!
Friday, November 09, 2012, 1:00 PM - 2:15 PM – Marco Russo – Room 612
Inside DAX Query Plans (BIA-403)
Discover the query plan for your DAX query and learn how to read it and how to optimize a DAX query by using these information.

If you meet us at the conference, stop us and say hello: it’s always nice to know our readers!

Optimize SUMMARIZE with ADDCOLUMNS in Dax #ssas #tabular #dax #powerpivot

sqlbi — Tue, 04 Sep 2012 08:23:00 GMT

If you started using DAX as a query language, you might have encountered some performance issues by using SUMMARIZE. The problem is related to the calculation you put in the SUMMARIZE, by adding what are called extension columns, which compute their value within a filter context defined by the rows considered in the group that the SUMMARIZE uses to produce each row in the output.

Most of the time, for simple table expressions used in the first parameter of SUMMARIZE, you can optimize performance by removing the extended columns from the SUMMARIZE and adding them by using an ADDCOLUMNS function. In practice, instead of writing

SUMMARIZE( <table>, <group_by_column>, <column_name>, <expression> )

you can write:

ADDCOLUMNS( SUMMARIZE( <table>, <group by column> ), <column_name>, CALCULATE( <expression> ))

The performance difference might be huge (orders of magnitude) but this optimization might produce a different semantic and in these cases it should not be used. A longer discussion of this topic is included in my Best Practices Using SUMMARIZE and ADDCOLUMNS article on SQLBI, which also include several details about the DAX syntax with extended columns. For example, did you know that you can create an extended column in SUMMARIZE and ADDCOLUMNS with the same name of existing measures? It is *not* a good thing to do, and by reading the article you will discover why. Enjoy DAX!

DAX Studio 1.0 beta available #dax #tabular #powerpivot

sqlbi — Mon, 30 Jul 2012 08:10:00 GMT

Are you waiting for a usable and interactive DAX editor? Are you tired of using the MDX editor provided by SSMS as a temporary solution to write and execute a DAX query? The wait is over!

DAX Studio is a free Excel add-in that enables you to write a DAX query in a small dialog box that offer a productive environment, with a tabular object browser and syntax highlighting. You can drag and drop table and column names from the object browser to the editor, getting a proper DAX syntax for the objects (this is something that is not available in SSMS because you browse multidimensional data and the syntax used for objects is the MDX one). More interesting, you can run a query and project the result either in an Excel worksheet or in an independent table grid.

You can connect to an embedded PowerPivot workbook or to an external Tabular model published on Analysis Services. You can find a longer introduction of DAX Studio in a blog post written by Paul te Braak.

My favorite use of DAX Studio is writing and testing DAX measures. You can easily test them by using the DEFINE MEASURE syntax followed by a sample query that uses the local measure.

Once you are confident that the measure works well, you can copy and paste its definition into your PowerPivot or Tabular project.

This is just a version 1.0 and we look forward to enhance the features in the future. This is a CodePlex project so you can participate as a developer, as a tester or by just providing your feedback about bugs and desired features.

Distinct Count of Customers in a SCD Type 2 in #DAX

sqlbi — Mon, 09 Apr 2012 20:55:25 GMT

If you have a Slowly Changing Dimension (SCD) Type 2 for your customer and you want to calculate the number of distinct customers that bought a product, you cannot use the simple formula:

Customers := DISTINCTCOUNT( FactTable[Customer Id] ) )

because it would return the number of distinct versions of customers. What you really want to do is to calculate the number of distinct application keys of the customers, that could be a lower number than the number you’ve got with the previous formula. Assuming that a Customer Code column in the Customers dimension contains the application key, you should use the following DAX formula:

Customers := COUNTROWS( SUMMARIZE( FactTable, Customers[Customer Code] ) )

Be careful: only the version above is really fast, because it is solved by xVelocity (formerly known as VertiPaq) engine. Other formulas involving nested calculations might be more complex and move computation to the formula engine, resulting in slower query.

This is absolutely an interesting pattern and I have to say it’s a killer feature. Try to do the same in Multidimensional…

#DAX Query Plan in SQL Server 2012 #Tabular

sqlbi — Mon, 21 Nov 2011 12:15:00 GMT

The SQL Server Profiler provides you many information regarding the internal behavior of DAX queries sent to a BISM Tabular model. Similar to MDX, also in DAX there is a Formula Engine (FE) and a Storage Engine (SE). The SE is usually handled by Vertipaq (unless you are using DirectQuery mode) and Vertipaq SE Query classes of events gives you a SQL-like syntax that represents the query sent to the storage engine.

Another interesting class of events is the DAX Query Plan, which contains a couple of subclasses (Logical Plan and Physical Plan). I’d like to know more about internals of query plans, but there is still no documentation on that. However, you can still get some hint by observing its content for different DAX queries returning the same results.

For example, you should know that using RELATEDTABLE( table ) returns the same as CALCULATETABLE( table ). (in case you don’t know, read my PowerPivot book – DAX is coming, learning it early on PowerPivot is smart move.) But are they really equal? Should we prefer one against the other? By examining the SQL Profiler events, now I can say they are identical.

For example, this query:

EVALUATE

ADDCOLUMNS(

'Product Category',

"SubCategories", COUNTROWS( RELATEDTABLE( 'Product Sub-Category' ) ),

"Products", COUNTROWS( RELATEDTABLE( 'Product' ) ) )

produces exactly the same query plan (and calls to the storage engine) as the following one:

EVALUATE

ADDCOLUMNS(

'Product Category',

"SubCategories", COUNTROWS( CALCULATETABLE( 'Product Sub-Category' ) ),

"Products", COUNTROWS( CALCULATETABLE( 'Product' ) ) )

At this point, my suggestion is to favor the semantic – if you don’t have to put other filters, use RELATEDTABLE to simply follow the relationship!

Now back to writing the next book on BISM Tabular…