Few words about: Unary operators in SSAS

Today’s topic will be the unary operators in Analysis Services. They allow us to build custom rollup operations in hierarchies. In standard hierarchies, eg. Parent-Child child elements are aggregated to parent with one standard operator defined, in most cases it will be + operator. But sometimes we need to create something custom and then unary operators come up.

Unary operators allow us to build some interesting feature based on dimension table like this:

parent-child-table

This table shows that:

Total=LevelA+LevelB

LevelA=LevelA1-LevelA2-LevelA3

LevelB=-LevelB1

Continue reading

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: LAG and LEAD TSQL functions

In SQL SERVER 2012 we’ve got some new functions that can be useful in our daily work, today I would like to present my two favorite ones: LAG and LEAD.

LAG function is working only with OVER( ORDER BY) clause and access data from previous row. LEAD functions works in very similar way – it’s return data from next row. I hope next example will be good way to understand how it works.

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