Few words about: MDXMissingMemberMode

Today I would like to write few words about MDXMissingMemberMode. This is property of a dimension in SSAS and sometimes a need occur to change its default value.

MDXMissingMemberMode has three possible values:

  • Default
  • Error
  • Ignore

where Default is Ignore. When we leave default value, all references in our MDX code that point to non existing members of dimension hierarchy will be ignored and client tool like SSMS will return empty result, for example

select {} on 0,

[Customer].[Customer].&[186951] on 1

from [Adventure Works]

Return nothing because member with uniqe name [Customer].[Customer].&[186951] doesn’t exist. When we set MDXMissingMemberMode to Error then we will get:

Query (2, 1) The level ‘&[186951]’ object was not found in the cube when the string, [Customer].[Customer].&[186951], was parsed.

Default behavior of this property is needed in most cases when we deal with flexible hierarchies – because when member moves from one parent member to another it automatically changes his unique name – but sometimes it will be useful to change this default behavior.

Advertisements

Few words about: hiding shared dimension hierarchies only for one cube

Few words about: hiding shared dimension hierarchies only for one cube

Sometimes, in many scenarios developer must deal with many cubes in the same project and share dimensions between them. IT’s easy to set up something like this but try to imagine situation when one cube musts be browsed by fiscal dates and the others need to use calendar dates. It is not a problem when we don’t have shared dimensions in our solution but for some people it can be when we have shared dimensions. But don’t be afraid – there is very simple solution to achieve our goal.

Let’s look at AdventureWorks Multidimensional project. There are two cubes: Adventure Works and Mined Customers. Both of them use Date dimension – we need to set for Adventure Works to see only Calendar hierarchy and Mined Customers need to see Calendar and Fiscal hierarchies – let’s do this!

First of all open Cube designer of Adventure Works and go to Cube Structure tab. There is dimensions window and there we need to find Fiscal hierarchy:

Cube structure dimensions

The only thing that you need to set up Visible property of Fiscal hierarchy to False.

dimension properties

That is all! From now shared dimensions could be more flexible to use. Thank you!

 

Few words about: APPLY operator

APPLY operator is very useful feature that is not very common, so I would like to introduce you into one of the most powerful tools in TSQL.

So let’s begin by showing some theory about JOIN operators. INNER JOIN and OUTER JOINS (RIGHT,LEFT, FULL) are very common and they work in very similar manner, as you can see in the picture below, JOINS must have two sets that are predefined – query engine have to know about rows from both sides before it executes the query.

Image

APPLY operator doesn’t need to have predefined table from both sides! Image below can help you to understand how it works:

Image

Continue reading

Few words about: SQLDAY 2014!

 

Image

 

I am pleased to announce that I will be attendee in annual SQL Server conference called SQLDay. Here you have description of the event from their website(http://conference.plssug.org.pl/) :

SQL Day 2014 is a special event organized by the Polish SQL Server Users Group (PLSSUG) to celebrate the birth of the group. This time it’s the seventh birthday. Again, welcome to the capital of Lower Silesia, Wroclaw, a city of many cultures, innovations and meetings. Wroclaw is one of the oldest and most beautiful cities in Poland. Located at the foot of the Sudeten on the Oder River through by its numerous tributaries and canals, is a unique city of 12 islands and more than a hundred bridges. It is the greenest city in Polish – the one inhabitant of 25 m2 of green (not counting the green housing estate). Wroclaw is constantly changing, building, expanding and modernizing. This year’s event will be held at New City Stadium – the same place where the matches of EURO 2012.

This year’s conference formula is similar to the previous one: the conference will last three days, during which participants will be able to participate in several workshops (Pre-Conf), led by speakers of Polish and invited guests from abroad and dozens of technical sessions.

Venue:
The conference will take place in the Municipal Stadium in Wroclaw LINK
Date of the conference:
April 28 (workshops) and April 29-30 (conference sessions)

 

Few words about SQL Server services

Today I would like to write few words about SQL Servier Services. This topic is quite easy but some people doesn’t know basic things about services. We can manage services with two simple administration tools:

  • Windows Services
  • SQL Server Configuration Manager

Microsoft recommend to use second option – Configuration Manager (CM) – and if we can we should use this tool but not in every situation (as I will show lately).

Continue reading

Few words about: VBA functions in MDX

There is not well-known feature of MDX – embedded VBA functions. If we want to perform easy excel-like operations on the cube level we can accomplish that by using set of functions which are listed here.

How to use it? By using prefix vba! Like here:

WITH MEMBER vbaTest

AS

vba!left(“This is sample text”,8)

SELECT vbaTest on 0

FROM [Adventure Works]

And the result will be:

Continue reading