Search results matching tag 'DAX'

From 0 to DAX at TechEd Pre-Conference Seminar #dax #msteched #tee13

sqlbi — Tue, 07 May 2013 12:54:00 GMT

In June I and Alberto will deliver a pre-conference seminar at both TechEd North America (New Orleans, LA) and TechEd Europe (Madrid, Spain).

This day is a very good quickstart for those of you that still didn't complete one of our books, or those of you that missed one of our workshop about Tabular or PowerPivot. If you are planning to go to TechEd, you might also consider attending a full day about DAX, following the From 0 to DAX one-day seminar. Here are the links:

TechEd North America – From 0 to DAX Pre-Conference Seminar (New Orleans, LA - June 2, 2013)
TechEd Europe – From 0 to DAX Pre-Conference Seminar (Madrid, Spain - June 24, 2013)

And in case you are underestimating the importance of DAX in your future BI projects... read this blog post from Dandy Weyn - his privileged point of view inside Microsoft highlights how much DAX is important today and will be pervasive in the future!

Group Sales by Age of Customers #dax #powerpivot #tabular

sqlbi — Mon, 06 May 2013 12:16:00 GMT

I published an article describing how to implement the grouping of sales transactions by age of customer at the moment of the transaction by using PowerPivot or Analysis Services. The same pattern can be used also for any kind of banding operation, this specific case is useful also to recycle the formula that gets the exact age of the customer for each transaction.

An interesting point is related to performance optimization. The technique is based on adding a calculated column in a table that might contain millions of transactions. This is less expensive than adding a column that contains a foreign key and then a relationship between a table containing group definitions and the transactions table. Every relationship is expensive and generates additional structures (you can see more files in the Analysis Services database, too). Adding one or two columns that have a low number of distinct values (10-15 rows) usually has a lower memory cost than creating a relationship with another table. The article also contains PowerPivot examples for both Excel 2010 and Excel 2013.

If only I could decouple attribute visualization from physical structure, I would put these “degenerate dimensions” in a separate folder, because in this way such attribute will be included in attributes belonging to the fact table, which might not be so clear in presenting data. However, I understand that such a decoupling could make live very hard to DAX clients (but probably for MDX it could be not a big issue).

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

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.

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!

LASTNONBLANK and FIRSTNONBLANK functions work with any column #dax #powerpivot #ssas #tabular

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.

SQLLunch on April 23 in London and Cardiff #sqlpass #dax #sqllunch

sqlbi — Mon, 15 Apr 2013 08:52:00 GMT

On April 23 I will present DAX in Action in London and Cardiff at SQLLunch event.
How is it possible I will be in two places at the same time?
This will be a remote presentation delivered in two locations, where you can have lunch while watching the session.

What is this session about? This is the session description:

Tabular is the new SSAS modeling experience and DAX is the new language to use to create BI solution with Tabular. How does it compare with MDX and Multidimensional? In this session, which is mostly based on demos, we will build a complex BI solution from scratch, starting from simple analysis and moving through complex scenarios, showing how you will leverage the tremendous speed of DAX to create complex solution on simple data models, focusing on the differences in building the same solution in MDX or DAX.

These free events are organized by the UK SQL Server User Group. If you are interested, you can register using the following links.
London : http://sqlserverfaq.com/events/534/SQLLunch-All-stuff-no-fluff-Marco-Russo.aspx
Cardiff : http://sqlserverfaq.com/events/535/SQLLunch-All-stuff-no-fluff-Marco-Russo.aspx

Pre-Conference Seminar about DAX at TechEd 2013 #msteched #dax #ssas

sqlbi — Thu, 14 Mar 2013 12:24:00 GMT

If you are using Microsoft BI stack and you still didn’t start learning DAX, you should not wait any longer. One of the option you have is starting with one of our books, or you can also attend one of our workshop about Tabular or PowerPivot. But if you are planning to go to SQLBits or TechEd, you might also consider attending a full day about DAX, following the From 0 to DAX one-day seminar. Here are the links:

SQLBits – From 0 to DAX Training Day (Nottingham, UK - May 2, 2013)
TechEd North America – From 0 to DAX Pre-Conference Seminar (New Orleans, LA - June 2, 2013)
TechEd Europe – From 0 to DAX Pre-Conference Seminar (Madrid, Spain - June 24, 2013)

Take a look at Early Bird expiration date – you can save a good portion of your budget by registering within March 22, 2013 at any of TechEd conference. For SQLBits there is a discounted price until April 7, 2013.

Even if I’m not blogging much in these weeks, I can assure you we are working on more DAX content that we’ll publish in the next months. Stay tuned!

Querying with DAX video available on Project Botticelli #dax #ssas #powerpivot

sqlbi — Mon, 25 Feb 2013 13:39:00 GMT

I recently published a video session on Project Botticelli web site regarding querying in DAX. You can write DAX queries to extract data from both PowerPivot and SSAS Tabular models. This could be particularly useful in three scenarios:

Reports in Reporting Services
Excel tables (used to populate other PowerPivot data models, for example)
Reports embedded in custom applications (i.e. ASP.NET pages)

Today there is a lack of tools to help you writing a DAX query, probably the best choice is DAX Studio, which I used in the video, but it has to be enhanced and completed. I hope to be able to work on that, but remember that other volunteers are welcome in a Codeplex project!

In this video I cover in particular the syntax of EVALUATE, CALCULATETABLE, SUMMARIZE, ROLLUP, ADDCOLUMNS, and ROW functions. The video is available to Project Botticelli subscribers.

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.