Search results matching tag 'Analysis Services'

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!

Interviewed in SQL Down Under podcast #sqlserver #ssas #powerpivot

sqlbi — Mon, 01 Apr 2013 18:03:00 GMT

I’ve been interviewed by Greg Low in SQL Down Under show 58, and this is *not* an April fool! We talked for one hour about Tabular, Multidimensional, Data Warehouse and just a little bit about music (you can discover which music genre I’m used to listen…).

You can hear this interview from the SQL Down Under Show 58 page (it is an MP3 format) and if you like it there are many other past shows available. The PodCast is also available on iTunes and you can hear other podcasts in SQL Down Under page on iTunes Preview.

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!

Cost of Process Defrag in Analysis Services Tabular #ssas #tabular

sqlbi — Wed, 13 Mar 2013 12:17:00 GMT

I recently received a question about the memory required to run a Process Defrag on a Tabular model in Analysis Services. The Process Defrag is useful when you run incremental processing of a table frequently, or when some of the values in the dictionary are no longer used in the table, for example if you process the same partition multiple times in a table and/or remove partitions from a table. Cathy Dumas wrote an interesting blog post about the savings you can obtain by running process defrag.

I made some investigation and I’ve been assured that data is not completely uncompressed in this process, even if some coding/encoding happens. In a rough estimate, you need a free space equivalent to the size of the table you are going to defrag (which is already in memory) plus buffer for transient data structure, but data are managed in a compressed form, without requiring larger memory buffers for uncompressed raw data. So, if all partitions of your table and the column dictionaries require 100MB in RAM, then you need another 100MB of free RAM in order to execute a Process Defrag.

Here is a more detailed description I received from Akshai Mirchandani:

In addition to the master copies, it requires enough memory for a new dictionary, and the final compressed data + some small temporary buffer space for the transient data structures (no big buffers like normal processing).

It is essentially going to read each column value, insert it into a new dictionary, get a new DataID back from the dictionary insert, and append that DataID to the current segment. It doesn’t need to do VertiPaq (compression) again, and it doesn’t keep the uncompressed data in buffers like the data processing algorithm does.
The important point here is that the analysis of the segment data to come up with the best compression strategy no longer needs to be performed – and that’s typically the most expensive step of the compression (the VERTIPAQ_STATE in DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS shows whether this was done).

This is a good news if you are concerned with memory required to perform this operation.

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

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

Microsoft BI Security White Paper #ssas #powerpivot #sharepoint #ssrs

sqlbi — Mon, 11 Mar 2013 11:47:00 GMT

There is a new whitepaper from Microsoft, Microsoft BI Authentication and Identity Delegation, which describes all the authentication and delegation scenarios with Microsoft BI technologies:

Personal BI Scenarios (Excel)
Team BI Scenarios (SharePoint)
Corporate BI Scenarios (Reporting Services, Analysis Services)
Federated BI Scenarios (Multi-Forest AD, Extranet, Cloud)

This is the new reference whitepaper to correctly plan and configure the security environment of a BI solution based on Microsoft BI stack.

Workshop and Events in Europe next month #ssas #tabular #powerpivot

sqlbi — Thu, 21 Feb 2013 09:41:00 GMT

As I mentioned a couple of weeks ago, I and Alberto are starting to travel again, delivering SSAS Tabular Workshop and other free community events around Europe. This is the updated list of our next dates, including community events:

Online – SSAS Tabular Workshop on February 27-28, 2013 (17-24 CET)

This workshop is delivered online – you can attend wherever you are
Timeslot is late afternoon-evening for Europeans, more appropriate office time for US market

Aarhus, Denmark – SSAS Tabular Workshop on March 12-13, 2013 (9-17 CET)

Alberto Ferrari will be the teacher in these dates
You can still register here

Aarhus, Denmark – Excel 2013 PowerPivot in Action on March 12, 2013 (17:30-19:30 CET)

Free community event where Alberto will show the capabilities available in Excel 2013 using PowerPivot and Power View
More info here

Utrecht, The Netherlands – SSAS Tabular Workshop on March 25-26, 2013 (9-17 CET)

Marco Russo (me!) will be the teacher in these dates
You can register here (early bird expires on March 11, 2013)

Veenendaal, The Netherlands - Querying and Optimizing DAX on March 25, 2013 (18-21 CET)

Free community event where Marco Russo (yes, always me!) will show how to write and optimize queries in DAX
More info here and free registration here

Oslo, Norway – SSAS Tabular Workshop on April 8-9, 2013 (9-17 CET)

Alberto Ferrari will be the teacher in these dates
You can register here

Milan, Italy – PowerPivot Workshop Full on April 17-19, 2013 (9-17 CET)

Alberto will be the teacher in these dates
Be careful: this workshop will be delivered in Italian language – a good excuse to practice your Italian visiting Italy for work reasons!
You can register here

Other workshops will follow in May and June in Finland, UK, Belgium and Sweden – a complete list of Tabular Workshop here and PowerPivot Workshops here.

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.

Hardware Sizing Guide for #ssas #tabular

sqlbi — Tue, 12 Feb 2013 14:10:00 GMT

A common question I receive is about how to correctly size a server for an Analysis Services Tabular instance. I always answer that an analytical evaluation is partly an empiric process, because there are many variables involved and the simplest approach is building the database model on a development server and then performing some workload test to understand how much memory and CPU is required. Another rule of thumb is to buy as much memory as you can. But this is not a simple approach if, for example, you want to optimize the size of a virtual machine (considerations about the opportunity to create a virtual machine with hundreds of GB of RAM are material for another post…).

Microsoft published an interesting whitepaper: Hardware Sizing a Tabular Solution in SQL Server Analysis Services. This whitepaper explore in detail the operations required to estimate the required amount of RAM and CPU for a given tabular model. My suggestion is to read the whitepaper carefully, because buying hardware before the development of the solution could be a very bad idea. The best strategy is to allocate a budget at the beginning of the project and to delay the buying of the production server as soon as possible, so hopefully you will get more memory and faster CPUs at the same price. I’d like to quote a statement from the Hardware Configuration Examples that probably many people didn’t believe when pronounced by me at some conference – now that it’s on a Microsoft whitepaper I hope there will be more consensus on this!

Anecdotally, we know that tabular models sometimes perform better on faster, newer processors than on high-end server hardware. Workstations that offer more in terms of raw processor performance are often first to market. When evaluating hardware, broaden your search to include workstations that you might not otherwise consider.

The whitepaper has a lot of practical action to get real numbers and provides you a practical method to get a good estimate. There are also many links to documentation and articles, some of them from this blog and from SQLBI website.