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

20. prosince 2018

Tabular a Power BI - velikost modelu

Úvod
Power BI uživatelé, vydržte, článek bude relevantní i pro Vás :) Možná :)
Poměrně častou otázkou, kterou dostávám na konferencích a školení týkající se analytických služeb je: Kdy použít Multidimensional, kdy použít Tabular (Power BI běží na Tabularu).
Rychlá odpověď úplně neexistuje (a možná si to zaslouží samostatný blog post). Jedním z faktorů vstupujících do rozhodování je architektura úložiště.
Multidimensional drží data na disku, Tabular v paměti. Sami si odpovězte na otázku, čeho mají vaše servery více :)
Je ale opravdu třeba se obávat nedostatku paměti? Tabular a Power BI do paměti data komprimuje.
O tom, jak komprese ve Vertipaq úložišti funguje jsem mimo jiné povídal na WUG Days a záznam můžete shlédnout zde
Konkrétně popis enginu na přednášce vychází z knihy od Alberta Ferrari a Marca Russa:
Deffinitive Guide To DAX
Bude vycházet druhá edice, chcete-li komplet přehled, počkejte si.

Priority vývojového týmu Analytických služeb jsou zřejmé. V Tabularu je budoucnost, Multidimensional se více nerozvíjí
(průlomových novinek v Multidimenzionalu jsme se nedočkali od verze 2008) a podpora ze strany klientských aplikací, zejména Power BI, pokulhává.
A to je to, co mě v poslední době vadí nejvíce a přehodnocuji, zda držet se multidimenzionalu je dobrý nápad (a to jsem velký fanda).
Multidimenzional byl pro stávající řešení ve firmě vybrán hlavně s ohledem na funkci. Tabular nesplňoval všechny funkční požadavky, po pár letech je ale situace již trochu jiná.
Na co však v Multidimenzionalu marně čekám a začíná se z toho stávat skutečná bolest jsou session level výpočty na úrovni reportu při živém připojení. Bolestí je víc.
Udělám z toho samostatný blog post v angličtině, protože jeden moudrý člověk mi řekl ohledně mých MVP aktivit. “Chceš-li něco změnit, musí se to dostat k produktovému týmu”.
Takže přemýšlím i nad tím, jestli budu psát česky, střídat jazyky, nebo přejít komplet do angličtiny (abych lépe ovlivnil vývoj produktu). To je ale jiný příběh.
Suma sumárům, začínám ve firmě silně uvažovat nad předělání stávajícího multidimenzionálního řešení na Tabular
(pokud nebudou mé prosby vyslyšeny, nic jiného mi asi nezbyde).
Předělat něco, co vznikalo cca 4 roky nebude ale na den. Takže se můžete těšit i na návazné blog posty.

Hlavní část
Mám multidimenzionální kostku cca 11 measure groups, 27 dimenzí. Na disku v MOLAP storage zabírá 86 GB.
První základní otázka zní
Pokud bych stávající řešení chtěl předělat do Tabularu, kolik paměti si vezme model, ve kterém budu mít dostupná stejná data?
Vytvořil jsem model obsahující všechny Measure Groups. A většinu dimenzí.
Některé malé jsem vynechal, protože by cvičení trvalo zbytečně mnoho času a vliv na velikost by byl beztak minimální.
K analýze velikosti jsem použil Vertipaq Analyzer od SQLBI (https://www.sqlbi.com/)
Nástroj je ke stažení zde včetně tutorialu. V podstatě se jedná o Power Pivot model postavený nad metadaty SSAS. Dá se použít jak pro analýzu Tabularu, tak Power BI.
Stačí změnit connection string na vaše SSAS a aktualizovat data.
Výsledky měření bez jakékoliv optimalizace
Celková velikost databáze 65,6GB bez jakékoliv optimalizace. Nejvíce místa si vzaly 2 největší faktové tabulky.
31 GB availability stock (539 milionu záznamů velká tabulka týkající se dostupnosti zboží) a 27,3 GB stock
(1,8 miliardy záznamů velká snapshotová tabulka týkající se skladových zásob).
Fakta jsou za poslední 3 fiskální roky, u nás tedy nyní cca 2,5 roku dat. Delší historii držíme v datovém skladu. Tyhle dvě faktové tabulky si vzaly 88,76% celkové velikosti.
Význam vybraných sloupců ze screenshotu:
Cardinality - u tabulky počet řádků, u sloupce počet unikátních hodnot
Table size - celková velikost tabulky (Columns size+User Hierarchies Size+Relationship Size)
Columns total size - velikost dat ve sloupcích (Data size, Dictionary size, Columns Hierarchies Size)
Data size - velikost detailních dat
Dictionary size - velikost slovníku souvisí s kompresí detaily o kompresi můžete dozvědět v článku od Alberta a Marca zde

Z předchozího screenshotu je tedy zřejmé, že Availability stock zabírá skoro 31GB a z toho 22,5 GB tvoří slovník.
Tabulka je menší do počtu řádků, ale větší do konzumované velikosti v paměti.
Jak jsou na tom asi jednotlivé sloupce?
Všimněte si žlutých buněk. Tabulka 30,9 GB. Sloupec forecasted_sales_units 26,5 GB. Tedy 85,89%. Data ve sloupci 2GB, slovník pro kompresi k datům 21 GB.
To je trochu nepoměr a prostor pro optimalizaci.
Pokud vím, jak v tabularu funguje komprese, vím že velikost slovníku je dána ovlivněna datovým typem. Datový typ totiž ovlivňuje kardinalitu.
Zkontroluji datový typ u sloupce a vidím float. Můžu zachovat funkčnost modelu a při citlivé změně datového typu nepřijít příliš o přesnost?
Decimal (19,4) by mohl stačit. Provedu reload a podívejte na číla.
Změna datového typu srazila velikost tabulky z 30,9 GB na 5,8 GB. Databázi to dostalo z 65,6 GB na 40,5 GB.
Konkrétní sloupec forecasted sales units jsem dostal z 26,5 GB na 1,3 GB. To je slušné změnou datového typu u jednoho sloupce :)
Závěr

Jak bude velký model v Power BI, nebo Tabularu se nedá dopředu odhadnout jen na základě velikosti vstupních dat.
Bude záležet na množství faktorů, zejména schopnosti vertipaq engine data komprimovat. Tento článek by vám měl dát drobný návod jak na analýzu využitého místa.
Co se týká potenciální konverze stávajícího multidimenzionálního řešení do Tabularu.
Odpověď na první otázku, bude místo problém zní. Místo problém nebude. I rychlost výpočtů pro základní metriky nad tabulkou s 1,8 miliardou záznamů byla velmi slušná.
Je to ale běh na dlouhou trať a je třeba zodpovědět další dotazy. Jako například:
Půjde pokrýt celá funkčnost? Nepůjde rychlost do kytek, jak se začnou věci komplikovat složitostí byznys logiky? O tom někdy potom :)

13. prosince 2018

Monitoring BI Solution using Power BI (presented on SQL Saturday Prague)

Intro:
Tento článek píši v angličtině. Téma jsem prezentoval na SQL Saturday Praha též v anglickém jazyce, kde byla značná část publika mluvící jiným jazykem (a nemám na mysli jen bratry a sestry ze Slovenska :) ) Dále již tedy v angličtině.

I will write this article in English and this is because I had a session on this topic on SQL Saturday Prague also covered in English. Significant part of audience was not speaking Czech (and I'm not talking about brothers and sisters from Slovakia only). Therefore rest of blog post in English

Blog post:
When doing anything it is good to have feedback if you are doing right. How to know if you are doing BI right? If your end users are using your outputs they are either happy with them (good for you) or they are using them because they have to (and if not happy with content and/or performance they will usually tell you).
If they have access to report and not using it? There is something wrong about it. Especially if you spent several weeks building output for particular user and now see zero usage. And that is it. Monitoring will tell you if your effort was successful or if you did all for nothing (paycheck will maybe ease your pain, but not completely).
So how do you get feedback about usage of your BI stuff?
By monitoring usage.
If your end user complains about slow SSRS report, what can you do about it? Improve it if you know the reason. How do you get to know reason?
By monitoring report executions. And analyzing performance related data.

In this blog post I will focus just on analyzing SSRS reports usage and OLAP usage (as on SQL Saturday)

SSRS
To analyze performance and usage you will need 3 tables in ReportServer database.
First of all
dbo.ConfigurationInfo - property ExecutionLogDyasKept needs to be changed from default 60 if you want to analyze data over longer period then 60 days
then you will need dbo.Catalog - list of reports, folders
ExecutionLogStorage - main table containing interesting stuff, can be joined to Catalog by connection Catalog.ItemId = ExecutionLogStorage.ReportId
At the end of this blog post will follow link to sample file created during presentation.
Tables described in data model:
Executions = ExecutionLogStorage
Reports = Catalog
OlapQueryLog = OlapQueryLog

DAX generated tables
Date = CALENDARAUTO()
Users = DISTINCT(UNION(DISTINCT('Executions'[UserName]),DISTINCT('OlapQueryLog'[MSOLAP_User])))
Measures of interest including DAX formulas to calculate it in blue
I'm interested in number of executions. Could be calculated in DAX as
Total Executions = COUNTROWS('Executions')
Also interested in Distinct Users of reports
Distinct Users = DISTINCTCOUNT('Executions'[UserName])

For performance troubleshooting we can break execution of SSRS report into
Time to return dataset (ExecutionLogStorage[TimeDataRetrieval])
Intermediate (format independent) report format creation contains data and layout, report level formulas ExecutionLogStorage[TimeProcessing]
Rendering (to specific format) - mhtml, Excel, Pdf, etc. ExecutionLogStorage[TimeRendering]

I would be also interested in BytesCount. If this number is high, execution on report server can be already finished on server, but it will take some time to render it on client (BytesCount then sent over network).

DAX Calculated column
Execution Time = (Executions[TimeRendering]+ 'Executions'[TimeDataRetrieval] + 'Executions'[TimeProcessing])/1000
Measures
Average Execution Time = AVERAGE('Executions'[Execution Time])
Average Data Retrieval = AVERAGE(Executions[TimeDataRetrieval])/1000
Average Data Rendering = AVERAGE(Executions[TimeRendering])/1000
Average Data Processing = AVERAGE(Executions[TimeProcessing])/1000


RequestTypeID 0 is adhoc execution, 1 is subscription.

OLAP usage analysis
For analysing OLAP usage you can enable loging on SSAS instance for both multidimensional and tabular. To avoid too many queries stored there is default sampling 10. Every tenth query will be stored. But to get general idea it is sufficient.
OLAP Executions = COUNTROWS('OlapQueryLog')

Then I can just create 2 common dimensions for calendar and distinct users (see DAX above). Create relationships between tables.


And create a report

Sample report is available here:


It can be downloaded here

Conclusion
This blog post was about providing resources to attendees of my session on SQL Saturday Prague. To other readers it should give idea how to monitor your BI landscape and get some information about usage and performance related metrics. File shared here is far from enterprise ready, but can be used straight ahead. Just change connections from localhost to your servers and remove last steps in Power Query transformations (I have to fake data, so I used first, last 4 for user names and report names).
If you want something finer tuned you can check out tool by my friends from Joyful Craftsmen (who participated heavily on SQL Saturday Prague). You can check out their tool here
Enjoy and if you have any feedback, let me know. 

12. listopadu 2018

SSRS reporty v Power BI službě

Do Vánoc měsíc a půl, ale Power BI tým přišel s nadílkou už nyní. No a udělali mi docela radost. Již nějakou dobu není tajemstvím, že se chystal SSRS typ reportů do Power BI služby. Nyní se očekávané stalo skutečností. Dostupnost zatím jen v Power BI Premium (těžko říct, zda to tak zůstane, ale nedivil bych se).

Pokud byste ale chtěli SSRS reporty v Power BI službě vyzkoušet, můžete požadavek na Premium obejít díky Power BI Embedded v Azure.
Není to dostupné ve všech SKU’s, ale od A4 nahoru, což je docela vysavač na kreditku, nebo MSDN kredit.
Chcete-li zkoušet, tak si to tedy rozmyslete dopředu, co konkrétně. No a hlavně následně nezapomeňte na tlačítko pauza ;-)
Po startu je potřeba v Power BI nastavit capacity settings a povolit typ zátěže “Paginated reports”.
Samotná publikace reportu probíhá přes get data- file- local file. Najdete rdl soubor s SSRS reportem. A vypublikujete.
Budete muset nastavit datový zdroj v Gateway.
Jakmile tohle provedete, vidíte povědomé “loading report”


A jede to i na mobilu :) Export do Excelu fungoval taky pěkně.
Závěr

Funguje to a nejen nad demo soubory od Microsoftu, vyzkoušeno nad vlastním reportem nad SSAS.
Milý Ježíšku, kup mi do firmy Power BI Premium. Prosím prosím.

30. srpna 2018

Záznam přednášky - Reportovací platforma společnosti Microsoft

Střihači videí se do toho pořádně obuli. Dnes s Vámi můžu nasdílet další záznam přednášky, tentokrát z WUG Praha. Téma Reportovací platforma společnosti Microsoft. Co se technologií týká, hlavní zaměření na Power BI a Reporting Services
https://www.wug.cz/zaznamy/495-Reportovaci-platforma-spolecnosti-Microsoft

Díky střihačům za odvedenou práci :)

23. srpna 2018

SQL Server Bootcamp 2017 a 2018 - záznamy přednášek

Díky Davidovi Gešvindrovi, který nejen SQL Bootcamp výraznou měrou umožnil uskutečnit, ale i v velmi rychle po akci sestříhal záznamy vybraných přednášek, se s Vámi mohu podělit o následující videa.

Power BI - Best Practices - https://www.wug.cz/zaznamy/483-SQL-Server-Bootcamp-2018-Power-BI-Best-Practices/
BI řešení pro ne BI lidi - https://www.wug.cz/zaznamy/480-SQL-Server-Bootcamp-2018-BI-reseni-pro-ne-BI-lidi/
Dynamický partitioning OLAP kostek s použitím SSIS - https://wug.cz/zaznamy/492-SQL-Server-Bootcamp-2018-Dynamicky-partitioning-OLAP-kostek-s-pouzitim-SSIS
Power BI Import Dat, Živé připojení a kompozitní modely - https://wug.cz/zaznamy/491-SQL-Server-Bootcamp-2018-Power-BI-Import-dat-zive-pripojeni-a-kompozitni-modely

Materiály k přednáškám jsem přikládal v rámci tohoto článku http://www.neoral.cz/2018/08/sql-server-bootcamp-2018.html


------------------------------------------------------------------------------------------------------
SQL Server 2017 se konal pravda již před rokem a jedno video se zaseklo ve střižně.
Můžete se též podívat na záznam přednášky
Úvod do MDX jazyka - https://www.wug.cz/zaznamy/473-SQL-Server-Bootcamp-2017-Uvod-do-MDX-jazyka/
Pokud byste chtěli i nějaké materiály nad rámec přednášky, můžete si přečíst MDX tutorial, který jsem napsal: http://www.neoral.cz/2016/01/mdx-tutorail-0-rozcestnik.html