27. prosince 2019

Záznam přednášky - Power BI vše, co nejen začátečník potřebuje vědět

Záznam přednášky z WUGu
Přednáška je určená primárně lidem, kteří Power BI svět objevují, ale přijdou si na své i pokročilejší uživatelé. Začneme terminologií a budeme postupovat tvorbou reportu pozpátku od prezentační vrstvy (vizuální možnosti) přes tvorbu výpočtů a datového modelu, až po načítání dat. Dále se podíváme na optimalizaci řešení, administraci a možnosti sdílení.

25. listopadu 2019

DAX for Analysis Services Multidimensional workaround

I have already write a blog post about existing limitation when using Power BI live connected to Analysis Services Multidimensional here. But recently I found out workaround which used not to work but does work now (and hopefully it will stay this way). But first brief introduction of the business problem
(if you can’t wait just scroll down to workaround part).
Introduction
Analysis Services can be installed in 2 main flavours. Tabular which is used in Power BI backend (first version introduced in Power Pivot for Excel 2010) and Multidimensional much older then that.
Native language of Multidimensional is MDX, native for Tabular is DAX. 
In theory you can query both installation types using both languages and I do use MDX to query tabular a lot when developing Reporting Services reports.
And I also tried the other way around to query Multidimensional with DAX. And it works in management studio.
For approximately 3years I’ve been waiting for option to write custom DAX measures when live connected to SSAS MD.
I thought that something small has to be developed in order to make it work and it is not a priority. After digging deeper and talking to product team I found out it is exactly the opposite case. 
DAX works on tabular structures and Multidimensional structures are just way too different. So some principles like iteration over table in case of SUMX wouldn’t work and doesn’t make sense.
In better cases DAX just wouldn’t work in worse cases it would lead into unpredictable results.
So the team did extra work in order to block it in UI of Power BI Desktop.
But this blocking all DAX leads into cases where you need something simple like Sales in Thousands in Power BI table and you are forced to change calculate script of your enterprise SSAS
model for sake of Power BI report...
And this is unfortunate at least.
Word of caution
But before you’ll apply the workaround just keep in mind that in can lead to incorrect results when using more complex DAX or it simply won’t work.
If it doesn’t work for you don’t blame Microsoft and don’t blame me :)
Workaround
It is rather simple first create blank Power BI workbook connected to SSAS Multidimensional using live connection
If you try to create new measure right click measure group and you can just expand all/collapse all.
(And last few years I was just few minutes before collapse because of this limitation)
Then save the file and publish to Power BI Service. I named it SD_PRD_Main (Shared Dataset PRoDuction Main) PBIX

Then create second blank Power BI file and choose Power BI Dataset as a source
Search the list
Here you go :)
Conclusion
Use it with caution, more complex DAX doesn’t have to work as stated above. But simple stuff like Measure in thousands. Actual vs Target etc... These can be done directly on report level.
This approach has other benefits like having one centralized connection string which can be used in 20-50 and more reports.
So you just change one connection if you need to perform failover to different environment/server.
Only outstanding problem I now have is how to change existing reports in batch to map them from SSAS MD live connection to Power BI Dataset as a source.
I heard it could be possible using Power Shell but I wasn’t able to make it work.

Enjoy your report level calculations. If it doesn’t work for your don’t complain to Microsoft. Because if you do, they could block the feature for good ;)

8. listopadu 2019

Jak obejít omezení Power BI u live connection

Na různých konferencích a přednáškách jsem již mnohokrát říkal, proč používáme ve firmě pro většinu Power BI reportů metodu živého připojení do Analysis Services a proč používáme
Multidimensionální kostky.
Důvody pro živé připojení jsou například počet aktualizací za den, centralizace business logiky pro všechny reporty ze stejné kostky.
U Power BI Pro v neposlední řadě velikost datového modelu (1GB u Pro).
Na druhou stranu tento přístup má i stinné stránky. Dlouhodobě mi u Analysis Services vadila vázanost PBIX reportu na Dataset.
Když jste chtěli přemapovat report na druhý server, museli jste stáhnout PBIX, změnit vevnitř připojení a přenasadit report.
U jednoho reportu z kostky není problém. Ale pokud jich máte 20, 30.. 100 ze stejného datového modelu... Failover ručně dělat nechcete.
Tento problém se dá řešit Power Shellovým skriptem (popíšu někdy příště).
Druhý problém který mi vadí a nad kterým jsem se vyplakal v samostatném článku a pro větší dosah článku raději rovnou v angličtině je nemožnost dělat DAX výpočty u živého připojení do SSAS Multidimensional.
To je problém například v případech, kdy potřebujete měřítko, které už v kostce máte. Například „Částka bez DPH“ a uživatel v reportu chce vidět tu stejnou částku ale v tisících v rozpadu v tabulce.
Momentálně není jiné cesty, než rozšířit calculate skript kostky (korporátního enterprise datového modelu).
A přidat tam kalkulaci, kterou potřebujete do jednoho reportu. To je trochu nepoměr měnit datový model určený pro celou firmu kvůli reportu pro pár uživatelů.
Jaktože to nejde... A nebo?
Ve dnešním článku popíšu jak oba problémy když ne vyřešit, tak aspoň obejít a to bez nutnosti cokoliv skriptovat, nebo programovat.
Kouzlo spočívá ve sdílených datasetech. 
Stačí vytvořit první report s živým připojením do Analysis Services Multidimensional.
Dialog vypadá podobně jako na obrázku
Po pravé straně v seznamu polí když chci přidat novou measure, nemůžu viz další obrázek.
Můžu buď expandovat, nebo kolabovat.
A po pravdě poslední 3 roky jsem byl chvíli před kolapsem pokaždé když se nezobrazovaly správně formáty z kostky, nebo chtěl někdo vidět číslo v 1000
, nebo někdo chtěl něco jednoduchého, co nebylo v kostce.
 
Report ukládám a publikuji do Power BI služby pod názvem SD_PRD_Main. 
SD = Share Dataset 
PRD = Production (jen abych předešel spekulacím)
Vytvořím nový prázdný report a použiji Power BI dataset jako zdroj.
V seznamu datasetů vyberu název reportu z předchozího kroku.
A hle... Tvorba nového měřítka je již možná :)
Závěr

Co jsem tímto získal? Jednak možnost tvořit měřítka nad SSAS Multidimensional živým připojením.
Za druhé centrální bod odkud spravuji připojení do kostky Main.
Kdybych nyní udělal 100 reportů nad datasetem SD_PRD_MAIN, stačilo by změnit tento jeden zdrojový report s datasetem a přemapovat jej na druhý server
místo toho abych přiklikával
jednotky, desítky, stovky reportů :)
Mě tento objev udělal radost, doufám že i Vám. Momentálně stojím před problémem jak přemapovat stávající obsah nad tento sdílený dataset bez nutnosti kopírovat jednotlivé objekty.
Zatím ale narážím buď na možnosti API, nebo své schopnosti v PowerShellu (které jsou velmi omezené). Takže pokud se podařilo někomu v PowerShellu
přemapovat stávající objekty z jednoho typu zdroje na druhý u živého připojení, dejte prosím vědět.

18. září 2019

Záznamy přednášek SQL Server Bootcamp 2019

Na letošním SQL Server Bootcampu jsem měl 3 přednášky v krátkém sledu před odjezdem na dovolenou. Pokud jste se náhodou akce nemohli zůčastit. Nebo jste se akce zůčastnili a chtěli byste si informace připomenout, záznamy ze dvou přednášek jsou již na stránkách WUGu.

Personalizovaný reporting - pojednává zejména o Row Level Security v Power BI a SSAS.
https://www.wug.cz/zaznamy/526-SQL-Server-Bootcamp-2019-Jak-na-personalizovany-reporting

Optimalizace Power BI a SSAS datových modelů - má řekl bych samopopisný název :) Dozvíte se něco o tom, jak funguje vertipaq engine a jak využít znalosti ve svůj prospěch. Stejně tak se dozvíte o nástrojích, které můžete využít pro troubleshooting a ladění
https://www.wug.cz/zaznamy/527-SQL-Server-Bootcamp-2019-Optimalizace-Power-BI-a-SSAS-datovych-modelu
Intradenní reporting - přednáška pojednávala o požadavcích na reporting v průběhu dne. Jak se k tomuto problému postavit s Power BI a SSRS.
https://www.wug.cz/zaznamy/534-SQL-Server-Bootcamp-2019-Intradenni-reporting

10. září 2019

Měřítko v dimenzi aneb vlastní layout v Matrixu

K napsání tohoto článku jsem byl inspirován na SQL Server Bootcampu 2019 paní Leonou. Posílám pozdravy. Řešení jsme nakousli již na konferenci po přednáškách, ale možná se bude hodit i někomu jinému. Takže dobrá příležitost pro článek, ve kterém jsem snad ještě trošku vylepšil to, o čem jsme se bavili.
Vizualizace Matrix v Power BI Desktopu má následující sekce. Co na řádky, co na sloupce a která měřítka jsou hodnoty.
Představme si, že máme 3 základní měřítka Částka, Marže a Marže v % a potřebujeme pro ně zobrazit následující scénáře. Skutečnost, Plán a variance proti plánu.
Požadovaný layout:
Dostat ale některá měřítka na řádky a jiná na sloupce je v Power BI trochu problém. Vizualizace Matrix totiž umožňuje defaultně zobrazit data buď na sloupcích
 
Nebo v řádcích díky přepínači ve vlastnostech matrixu „Show on rows“ v sekci „Values“
Řešením je tvorba pomocné dimenze se seznamem toho, co chcete zobrazit na řádcích. V modelu jsem pojmenoval jako „Measure list“ přes zdroj „Enter data“
Poté můžete pomocí funkce SWITCH vytvořit tři metriky Skutečnost, Plán, Variance
Skutečnost = SWITCH(sum('Measure list'[ID]),1,[Castka],2,[Marze],3,FORMAT([Marze %],"0.0%"))

Plán = SWITCH(sum('Measure list'[ID]),1,[Castka Plan],2,[Marze plan],3,FORMAT([Marze % plan],"0.0%"))

Variance = SWITCH(sum('Measure list'[ID]),1,[Castka]-[Castka Plan],2,[Marze]-[Marze plan],3,FORMAT([Marze %]-[Marze % plan],"0.0%"))

A vytvořit požadovaný layout
Přikládám demo soubor pro inspiraci https://bit.ly/2maJZw9 

3. července 2019

Paginated Report Builder a vlastní dataset z Power BI služby

Je to již nějakou dobu, co vyšla varianta aplikace Report Builder pro Power BI Service. Jedná se o přebarvený Report Builder, ze kterého byly vyházeny věci, které nejsou relevantní pro Power BI Service. Jako například sdílené datové zdroje atd. Postupně do něj ale začnou přibývat nové funkce, které jsou naopak relevantní právě pro Power BI službu. Jedním z těchto vylepšení je možnost využít Power BI dataset jako zdroj (vytvořený metodou import). Originální blog post si můžete přečíst zde:
Jako člověk, který tráví v reporting services hodně času tvorbou reportů nad SSAS kostkami a Power BI je moje vášeň jsem si položil otázku, zda půjde vytvořit report s vlastním datasetem.
Datasety pro SSRS reporty si obvykle píšu v MDX jazyce (dá se pro Multidimensional i pro Tabular). Power BI služba má v pozadí tabular, tak by to neměl být problém.
Takže jak na to?
V prvé řadě si budete muset napsat vlastní dataset. Abyste vůbec mohli začít psát, musíte se připojit na “server”.
K tomu nám pomůže DAX Studio (ke stažení zde: https://www.sqlbi.com/tools/dax-studio/)
Pokud máte otevřený soubor s Data Importem vyberte jej, jako já na obrázku
V pravém dolním rohu se Vám zobrazí locaslhost:portnumber

Můžete otevřít management studio, vybrat typ serveru Analysis Services a napsat tam co jste se dozvěděli v DAX Studiu
V seznamu databází se objeví jedna se strašidelným GUID v názvu. Right click – new query – MDX (pokud preferujete DAX, stejný princip, jiná šablona dotazu).
Napíšete si Váš dotaz a odladíte v management studiu.
Otevřete Paginated Report Builder a mezi zdroji zvolíte Add Power BI Dataset Connection... Dialog Vás donutí přihlásit se do Power BI.
Najdete svůj dataset v seznamu a přidáte jej jako datový zdroj.
Right Click na datový zdroj, add dataset. Otevřete Query Designer (2 na dalším screenshotu)
Vyberete jazyk MDX (1) a přepnete z designeru do textového režimu (2)
Vložíte dotaz a pokud v dotazu nemáte parametry, nemáte problém :) Parametrizace reportu je nad rámec tohoto článku.
Dále jen pokračujete s tvorbou SSRS reportu, jako obvykle (tedy pokud s touto technologií pracujete) a může z Vás vypadnout i takováhle drilovatelná tabulka. 
Já vím, že není pěkná, ale tady šlo o proof of concept :)
Můžete publikovat report do Power BI služby (Premium feature). Pokud byste následně otevřeli vytvořený report v klasickém report builderu/Visual Studiu, můžete se podívat na connection string.
Report tam ale bez dalších zásahů spustitelný nebude. 
Jestli se nějak dá doinstalovat data extension PBIDATASET do klasických Reporting Services mi není známo.
Možná se zkusím poptat, protože by to bylo zajímavé.

5. června 2019

Power BI - Performance Analyzer

report je pomalý slyšel pravděpodobně každý vývojář reportů. Minimálně z těch, jejichž tvorba je používána koncovými uživateli :)
Což zjistíme díky logování a monitorování využití (psal jsem zde http://www.neoral.cz/2018/02/power-bi-monitoring-vyuziti-reportu.html).
Datovým modelováním a optimalizací datového modelu jsem se zabýval mimo jiné na WUG Days v této přednášce
(přetočte zhruba na 1:10:00). Pro ladění bylo potřeba v DAX Studiu zapnout tracing, vymazat cache, klikat, analyzovat atd.
Nyní se ale proces ladění Power BI reportů zjednodušuje díky Performance Analyzeru přímo v Power BI Desktopu (představeno v updatu květen 2019) a tom je právě dnešní blog post.
Performance analyzer zobrazíte na záložce view jako jedno ze zatržítek
Objeví se lišta s následující nabídkou
Pokud zapnete nahrávání, můžete provádět libolné interakce na reportu. Kliknul jsem refresh visuals a vypadlo tohle
Nejvyšší čas v milisekundách nemusí nutně znamenat největší problém.
Když rozbalím vizualizace s časem přes 3 vteřiny, uvidím rozpad na DAX Query, Visual Display a Other.
Nabízí se otázka, co který tento čas znamená. DAX query je doba pro vyhodnocení DAX dotazu. Visual display doba na zobrazení vizualizace záludně se tváří Other.
Dokumentace tvrdí, že Other pokrývá čas pro přípravu dotazu, čekání na ostatní vizualizace až doběhnou a cokoliv dalšího.
Takže z posledního obrázku je zřejmé, že Improvers a Decliners vizualizace dlouho trval DAXový dotaz, dalo by se s tímto něco dělat.
Tlačítko Copy query umožňuje dotaz vykopírovat do notepadu (kde toho moc neuděláte) nebo do DAX Studia, kde můžete ladit.
Najít například measure, která způsobuje problémy a řešit její optimalizaci.
U kategorie other bude ladění komplikovanější, protože se může jednat i o čas čekání, než doběhnou všechny ostatní vizualizace a to může být dáno jen počtem vizualizací na straně.

Každopádně je dobré, že se rozšiřují možnosti pro ladění přímo v Power BI Desktopu

26. dubna 2019

DAX for SSAS Multidimensional


One of my major developer pains using Power BI is inability to create report level calculations in Power BI reports live connected to SQL Server Analysis Services Multidimensional. This affects my development for 2+ years and after trying to persuade product team as MVP with no success I decided to go public to support my cause and hopefully fix the problem for me and others by setting up Power BI Idea and let it vote up by public.
First, I would like to bring little bit of context. Currently I work on a project that started prior I joined the company approximately 5 years ago. Solution was built in SSAS multidimensional and is still extended even now. From SSAS we’re reporting using different technologies on Microsoft platform SSRS, ad hoc analysis in Excel and Live Connected Power BI reports.
Why live connection to cube?
Because of data size and shared business logic, that can be used in different reporting tools. Specifically measure definitions for Measures, Time related calculations, Time and Target Variances etc. Security model with Row Level Security. I don’t want to duplicate this logic in multiple data models using data import, because it would be a huge pain to maintain if logic changes for any of these. I need central definition, this is top to bottom BI Delivery.
Why not (yet) tabular?
As stated above, project started 5 years ago and Tabular by the time wasn’t there where it is now (not as Multidimensional). I agree, there were great improvements in Tabular in last 5 years and if I would stand in front of new project, I would probably take it as my first choice. But... The solution is already built. One of main motivators were time calculations stored as utility dimension (one definition working for all measures) and I know this is coming to SQL Server 2019, but I must use what I have in production now.
Truth is there is no straight forward way to migrate Multidimensional solution to Tabular without months of developer resources which we don’t have available, because new business requirements are coming. Even if we had time and resources, current solution contains a lot of logic in calculate script and I’m not 100% sure how I would model it in Tabular without refactoring DWH layer as well.
I’m sure there are other companies/consultants doing Top to Bottom BI Delivery and are stuck now with multidimensional. Please prove me right :-)
Why DAX doesn’t work against Multidimensional cubes?
And that is the point. DAX works against Multidimensional. Thanks to BISM (Business Intelligence Semantic Model) you can query Tabular and Multidimensional using MDX and you can query them by DAX as well. I’m personally using MDX against Tabular data models a lot when developing SSRS reports. And I know I can do it the other way around. It works.
So why it doesn’t work in Power BI?
Tabular works on totally different principals then Multidimensional. Multidimensional doesn’t understand concept of table and relationships as tabular interprets them. So totally different semantics of languages. Some DAX functions won’t work for Multidimensional as tabular. So Power BI team is blocking the functionality of DAX measures against SSAS Multidimensional on purpose from the beginning and had to implement UI changes to achieve that.
Problems with Power BI live connected to SSAS MD
I won’t cover here all the problems I have, just few examples. Even though formats work alright in Excel when connected to cube (Excel is client issuing MDX queries), measure is formatted in Excel correctly, but not in some Power BI visuals. Have a look at this percentage example (which is still valid by the way) https://community.powerbi.com/t5/Desktop/SSAS-Live-Connection-Formatting-Issue/td-p/413419
Chart displays numbers as decimal, data preview table correctly as percentage. This doesn’t happen every time and that is why it is painful. If formatting works or not can be impacted by logic inside of calculate script.
Second for multidimensional live connected models you can’t +/- expand matrix visual.
And the main one for me is, you can’t create measures and this is what this whole blog post is about.
If I need something simple like “Sales Ex Vat in Thousands” and I have in my cube Measure Sales Ex Vat only. I must create calculated member in cube (enterprise data model), divide the number there by 1000 for sake of one Power BI report.
This is wrong!
What would help?
Ability to create report level DAX measures.
I could create [Sales Amount Ths] = [Sales Amount] / 1000
If I have problem with [Margin %], I could create measure [Margin %_] = [Margin %] and format it on report level.
If I need to create variance [Actual]-[Target], I could do it without side effects.
Why not?
If I would need to write complex DAX, with function that could potentially not work or provide incorrect results (which is the assumed concern here). It is my responsibility as developer to dev and test the functionality.
If intellisense won’t work here, I don’t mind. I can write the formula outside of Power BI Desktop, copy and paste.
Even if it is feature which needs to be explicitly enabled to avoid misuse by random user, I’m happy with that.
Some functionality is better than no functionality.
Changing enterprise data models for sake of one report if the calculation could be derived from data in the report is wrong.

If you agree with me. If you miss the functionality. If you would like to help me from my pain. Please support this idea and share


Thank you
Jiri