Archive for SQL Server

Goodbye Vertipaq, hello xVelocity !

Usually when a MS product finally goes to RTM, even if you’ve been playing with all CTPs and RCs, you’ll discover some ultimate (last-minute?) changes and new small features that wasn’t present before.

This one seems to be “just” a re-branding but may be significant of a new strategy for the (almost) new MS in-memory analytics engine technology : the Vertipaq engine which is now integrated in Excel through Power Pivot, Sharepoint through Power Pivot and PowerView, Analysis Services through Tabular Model and even in the Database Engine through ColumnStore Index (Codename Apollo) is now called xVelocity!

You can find the official announcement here on the Sql Server Team blog, and the usual strategy clarification here on the Analysis Services and Power Pivot Team Blog.

So … long live xVelocity !


Leave a Comment

Microsoft Techdays’12 Webcasts

Recently i blogged here on the Microsoft Techdays’12 event that took place in Paris a few weeks ago.

If you missed it or could not attend all your sessions of interest, you can now watch them all on the event’s website.

That’s great material for us to learn a little bit more on the MS products and to gather real life feedbacks on them.

Finally, just a little bit of ad for the session I made back then 🙂 : it’s on MDS and  you can watch the video right here.

Leave a Comment

Microsoft TechDays’12

In a few days from now I will be participating to the french edition of the MS TechDays’12  that will take place in Paris from the 7th to the 9th of February, and this year again I will be a speaker.

The session, that I will share with two colleagues of mine, will be a level 300 presentation about Master Data Services (MDS) and how MDS helps you to manage your master data :

SQL Server 2012: Gérer vos données maitres avec Master Data Services (MDS)

If you have the opportunity to attend it, don’t hesitate to join !

As an alternative, you may also want to review those other sessions, all conducted by colleagues from Avanade :

• Sharepoint 2010 et les utilisateurs infonuagiques : Gaël Fabry

• ALM 360° – Les fonctionnalités clés de Visual Studio et TFS pour booster vos projets : Philippe Puschmann

• Azure Integration Services : les concepts de BizTalk dans le Cloud : Olivier Sagory et Marius Zaharia

• Concevoir sa plateforme commerce multi canal en moins d’une heure : David Allaigre et Régis Ravant

Nouvelles tendances du poste de travail: “Bring your Own PC”, “Desktop as a Service”, une réalité en entreprise – Parcours Décideurs : Régis Ravant

Leave a Comment

[Master Data Services] “The http service located at xxx is too busy”

During the installation process of SQL Server 2012 RC0 / Master Data Services, you are asked to provide an administrator user account.

When connecting for the first time to the MDS web service via the new MDS Excel Add in for example, you may experience the following error : “The http service located at http://xxx is too busy”

The user account you entered earlier is used, among other things, to configure the default application pool that belongs to MDS. In particular, you may have noticed that you weren’t asked for the password. That’s exactly what’s missing.

Follow the following steps (from left to right) :

  1. Right click on you Computer icon on your desktop, choose “Manage”.
  2. Under “Services and Applications”, click on “Internet Information Services (IIS) Manager”.
  3. In the appearing pane, select “Application Pools”. Here you’ll find the “MDS Application Pool” application. Right click on it, and select “Advanced Settings”.
  4. Under “Process Model”, find the “Identity” property, click in the field and click on the “…” button. Choose “Set”. There you can enter the password.

Having spent 20 minutes to find out what was wrong, I thought it might help someone else to share my findings.


Leave a Comment

Analysis Services : How to add subfolders to your Measures Groups (Display folders tips and tricks)?

I’ve already blogged three times on measure groups matters and never thought I would have enough material for a fourth blog post. Fact is I stumble across an old post from Mosha Pasumansky on his sqlblog blog on the topic and rediscovered something I almost forgot with the years.

First you can find the three first posts here, although you’re not required to read them first to get the point of this post :

Analysis Services: What are the best practices for grouping measures ?

Analysis Services Feature Suggestion : Managing Measure Group visibility

Analysis Services : How to break distinct count measures into separate measure groups ?

To sum up Mosha’s blog post, you can add some extra flavour to your measures organization into folders with the following techniques :

– You already know that you can organize your measures into folders. But it is less known that you can have also sub folders. For doing this, as the UI in BIDS does not seem to allow you to do it, you jus have to enter the folders located on the path to the indicator separated by a “\” or “/” sign depending on your client tool. (ie : “folder 1\folder 2\…\folder x”)

– Even better, you can specify multiple folders to host the same measure by separating them with a “;” sign. (ie : “folder 1;folder 2;…;folder x”). In this case the measure will appear as much as the number of folders but it will act as one and single measure as selecting or de-selecting one of the clone will automatically impact the other instances.

Note : All those behaviors are client tool dependant as the information are only metadata attached to each measure and as nothing requires the client tool to make use of it. So depending on which tool you’re using, you may have to test its behavior before proposing those mechanism to you end users. I have tested them with success in Excel 2010.

Leave a Comment

Analysis Services : Beware of unused dimensions “out of sight, out of mind”

OLAP Cubes in general and Analysis Services in particular are very handy, easy to learn and powerful tools to find quick answers to huge and unexpected questions.

Once that’s said, one of the first thing I teach the end users I have a chance to meet during workshops is that they must never trust what they see without questioning the exposed data in the context of their functional specifications. Easy to learn, but yet hard to master …

Along the years I have gathered a list of common misconceptions about cubes and OLAP databases that I usually put in a good place in my workshops’ agenda. Just to be sure they won’t get disappointed several weeks/months later when they will get their hands on the solution we have built for them…

Here I want to discuss the mechanism by which dimensions that are not used in a query still have an impact on the result. In my list of misconceptions this one is called “Beware of unused dimensions : out of sight, out of mind”.

Indeed, that’s not because a dimension is not used in a query that it does not have an impact on the result and it is an extensive source of questioning/errors when you’re not accustomed to your cubes business rules.

In every cases the usage of those features must be thoroughly documented in the user documentation.

1) Left aside dimensions : impact of overridden default members

The most common source of problem  are the dimensions’ default members. By default, it’s the ‘All’ member, but it can as well be something else if you decide it so.

For example, when querying a stock indicator the default member of the time dimension will most probably be something like the last date for which we know the stock level as aggregating stocks over time at the ‘all’ level of the dimension has no meaning (There is other ways to achieve this including using SCOPE statements or LAST NON EMPTY aggregations, but that’s just for the purpose of my demonstration).

In case the dimensions left aside your query or pivot table do have their default members defined as their respective ‘All’ member, then no problem, everything goes as if they weren’t there at all. And as it is the most common scenario you may be mislead to think that this is always the case, but it’s not.

If I take back my stock example, you realize that if you leave your “snapshot time” dimension aside, your query gives you only the last known figures and not all known stocks from the beginning of times. That’s exactly because the left aside dimension has one of his member defined as default member. You can achieve the same result by explicitly filtering the query by the dimension custom default member meaning that it has indeed a filter action on the query context.

As a corollary to this, every dimension, used or not in a query, has a current member in the context of any query.

2) Left aside dimension’s hierarchies : how do behave unused hierarchies ?

A more subtile behavior occur when you are using hierarchies in your queries.

Take for example a standard “Time” dimensions with two hierarchies defined as H1 : “Year -> Week -> Day” and H2 : “Year -> Quarter -> Month -> Day”. The default member of the dimension is the ‘All’ member of the ‘All’ level.

When left aside, the current member of the dimension is ‘All’ and by extension, every hierarchy in the dimension inherit the same behavior.

Now here’s the questions :

Q1 : If I filter my query by using a member of the quarter attribute of the dimension, what is the current member of the left aside members ?

A1 : You’re right, it’s the ‘All’ member.

Q2 : And what is the current member of the left aside H1 hierarchy ?

A2 : Yep again, the ‘All’ member.

Q3 : And now, what is the current member of the left aside H2 hierarchy ?

A3 : If you answered the same member of the quarter level at the respective quarter level, then you guessed it right. That’s because hierarchies are really some kind of views over dimension attributes, so filtering attributes do have an impact on the hierarchies and vice-versa.

Sometime, when coding measures in MDX, you end up testing toward which hierarchy the formula should be evaluated as you have to explicitly quote the hierarchy in the formula when there are two of them or more. In this case testing if you are not on the default member doesn’t guaranty that the hierarchy is actually used as it can be left aside but filtered by the usage of another hierarchy or attribute as exposed earlier.

All in all, though it may seem straightforward to query cubes, particular attention should be accorded to the behavior of cubes when they grow in complexity.

Leave a Comment

Microsoft Days 2011 : Data Quality for datawarehouse projects

Recently, a colleague of mine and I were asked to record a webcast for the Microsoft Days 2011 Event.

It’s a french Microsoft event that took place between the end of september and november in 7 cities across the country.

In order to offer more food for thoughts to their visitors, Microsoft is asking to some experts in their domain to participate to webcasts.

Our webcast was focusing on Data Quality and its importance for datawarehousing projects. During the webcast we demonstrated why/how Data Quality Services (DQS) and Master Data Services (MDS) can be used together to achieve better data quality in our projects.

For those of you who are speaking french, you can take a look at it here.

Some of our colleagues have registered another webcast on Sharepoint 2010 which you can watch here.


Leave a Comment

Older Posts »