Kilka słów o: Direct Query w PowerBI

W najnowszej odsłonie PowerBI Desktop dostaliśmy możliwość bezpośredniego odpytywania źródła danych przy pomocy technologii DirectQuery! A co za tym idzie nasz model nie jest już jedynie statyczną kopią danych źródłowych wymagającą cyklicznych przeładowań, a jedynie interfejsem raportowym odpytującym źródło danych. Do tej pory większość modeli nad jakimi pracowałem czy to pod postacią Power Pivot czy też Power BI wymagała częstych bądź rzadszych odświeżeni danych, problem ten aktualnie został rozwiązany wraz z nową wersją opisywanego oprogramowania.

Jak zaimplementować DirectQuery w Power BI Desktop? Jest to bardzo proste – wybieramy GetData a następnie np. SQL Server

Continue reading

Advertisements

Kilka słów o: Procesowanie i partycjonowanie SSAS od podszewki

W ostatnim czasie miałem zaszczyt wygłosić prelekcję na temat procesowania i partycjonowania SSAS na największej konferencji technologicznej w Polsce poświęconej zagadnieniom bazodanowym i analitycznym tj. SQLDAY2015. Wideo z tego wydarzenia można obejrzeć poniżej.

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: 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

Few words about: SCOPE and THIS keywords

Today I would like to talk a little bit about two MDX keywords: SCOPE and THIS.  Below I would like to show how to use it and build something called subcube.

Subcube is a simple term when we talk about slice and dice our cube. For Example if our multidimensional cube has only one dimension attribute: Year and OrderQuantity as a measure, if we filter this dimension to have only year 2010 – it will be subcube of entire cube.

As you can see here, SCOPE syntax is very simple – in brackets we have to put a subcube expression. After this we can operate on specified subcube, if we want to change it we need to use another keywords – END SCOPE.  If we close our SCOPE with END SCOPE our script comes back to default scope(entire cube).

Continue reading

Few words about: Unknown member

Sometimes in my work i’ve got problems with integrity of data, especially when I need to build cube based on OLTP systems. The easiest way is to build multidimensional cubes  on DW but sometimes there is no possibility to have DW. Poor situation but Analysis Services give us opportunity to deal with it.

Perfect situation is when our solution has all data that it needs – every fact table refers to existing dimension row. But what if something refers to non-existing ID of our dimension we can easily solve this problem by setting UnknownMember property. Below I will show small demo how to recognize this problem and solve it.

First of all we need to build small cube project based only on two tables from Adventure Works DW:

  • FactResellerSales
  • DimProduct

After build Multidimensional project we should see something like this:

Simple schema of two tables Continue reading