Jeśli kiedykolwiek tworzyłeś model w SSAS Tabular albo PowerBI Desktop i ręcznie ustawiałeś formatowanie, poprawiałeś nazwy, dodawałeś prawie identyczne miary, to wiesz, że szybko można zacząć kwestionować swoje wybory życiowe. Klikasz w te same opcje raz za razem w nieskończoność… To nie jest praca, to katorga. Na szczęście istnieje lepszy sposób i właśnie o nim będzie ten artykuł.
Zapraszam do poznania Advanced Scripting w Tabular Editorze – narzędzia, które pozwala zautomatyzować powtarzalne zadania, oszczędzić czas i zachować resztki zdrowego rozsądku. Pokażę konkretne przykłady skryptów, przydatne triki i sposoby na to, jak sprawić, żeby model „robił się sam”. No, prawie sam.
Potrzebne oprogramowanie:
- Tabular Editor – np. wersja portable, artykuł opracowano przy użyciu wersji 2.27.2
- Power BI Desktop artykuł opracowano przy użyciu wersji 2.156.951.0 64-bit
Na potrzeby tego case study będziemy używać modelu SSAS tworzonego automatycznie przez Power BI desktop, jednak te same operacje można wykonać na dowolnym modelu SSAS Tabular oraz dowolnym modelu stworzonym w Power BI Desktop.
Jakie możliwości daje Advanced Scripting w Tabular Editor?
Najkrócej – ogromne.
Ze względu na relatywnie proste operacje i dobrą dokumentację tego procesu na stronie Tabular Editor oraz Microsoft, nie jest konieczna znajomość C# – najpowszechniejsze operacje można dopasować z opisanych przykładów i opracować intuicyjnie.
Wykorzystując proste skrypty C#, możemy:
- zautomatyzować tworzenie miar,
- dodać automatycznie opisy pól,
- zmienić wybrane znaki lub słowa w nazwach kolumn,
- dodać translacje,
- dodać miary zdefiniowane w osobnym pliku
i wiele, wiele więcej.
W tym artykule skupimy się na najpowszechniejszych operacjach, które mogą być użyteczne dla inżynierów analitycznych modeli danych.
Case study
Definicja modelu
Źródłem danych dla naszego przykładowego modelu będzie sztucznie wygenerowany zestaw informacji o przesyłkach.
Do rozpoczęcia pracy z Tabular Editorem potrzebujemy otworzyć model – w naszym przypadku wykorzystamy opcję otworzenia modelu z istniejącej bazy:
Z dostępnych opcji wybieramy Local instance oraz localhost:Przesylki.pbix – pamiętaj, by zobaczyć tu plik, musi być on w danej chwili otwarty na Twoim komputerze w Power BI Desktop!
Dodanie translacji ze zmienionym znakiem w nazwie
Częstym problemem przy importowaniu danych, szczególnie do Power BI Desktop, są mocno techniczne nazwy kolumn, które trzeba ręcznie zmieniać, niejednokrotnie przepisując tę samą nazwę, ale zamieniając znak podkreślenia na spację lub zmieniając wielkość liter. Z advanced scripting możemy zrobić to w parę chwil.
Wykorzystamy translację – funkcję umożliwiającą tworzenie wielojęzycznych modeli. Zależnie od wybranego języka nazwy obiektów widoczne dla użytkownika będą różne, mimo że fizycznie nadal jest to jedna kolumna (tabela, miara itp.). W pliku pbix mamy domyślną translację zależną od języka modelu, który sprawdzić możemy w opcjach pliku w ustawieniach regionalnych. W przypadku pliku, na którym pracujemy, jest to język aplikacji, czyli angielski. Sprawia to, że w modelu domyślnie mamy dodaną translację ‘en-US’.
W poniższym skrypcie nazwy przypisywane są do domyślnej translacji modelu, ale nie jest to zalecana praktyka – zazwyczaj dodajemy translacje dla innych języków. W innym przypadku plik może raz używać nazw oryginalnych, a innym razem przetłumaczonych.
Używając tabular editora, możemy dodać do modelu wiele różnych translacji. W artykule odwołujemy się do „en-US” jako przykładu.
W naszym modelu mamy nazwy zawierające _. Skrypt doda do kolumn translację, która zamiast tego będzie mieć spację. Kod, który musimy wkleić w zakładkę C#:
foreach (var col in Selected.Columns)
{
if (col.Name.Contains("_"))
{
col.TranslatedNames["en-US"] = col.Name.Replace("_", " ");
}
}
Po wklejeniu kodu wystarczy zaznaczyć kolumny, w których chcemy dokonać zmiany i nacisnąć zielony przycisk „run”:
Po sekundzie widzimy, że w kolumnach pojawiły się translacje, które nie zawierają już _, ale bardziej przyjazną dla użytkowników biznesowych nazwę ze spacją:
Co istotne, zmiana została wykonana tylko dla zaznaczonych kolumn.
Zmiany te nie są widoczne bezpośrednio w Power BI Desktop; staną się widoczne dopiero w Power BI Service po opublikowaniu raportu dla określonych ustawień języka. Translacje inne niż domyślna przetestować można także, podłączając się do modelu przez MS Excel i dodając do ConnectionString Locale Identifier=1045 (język polski).
Używając takiego Connection File w MS Excel, zobaczymy zmienione nazwy, jeśli dodaliśmy je dla języka polskiego do modelu:
Zmiana wielkości liter w nazwie
Zdarza się jednak, że nazwy kolumn w źródłach podawane są wyłącznie wielkimi literami. W przykładowym modelu dzieje się tak dla tabel County_origin oraz County_destination. Z advanced scripting możemy bardzo szybko sprawić, by nazwy były bardziej przyjazne dla naszych użytkowników.
Używając poniższego kodu, zamieniamy nazwy tak, by jedynie pierwsza litera była wielka, a reszta mała:
foreach (var col in Selected.Columns)
{
var lower = col.Name.ToLower(); // przypisanie do lower nazwy kolumny z literami zamienionymi na małe
col.Name = char.ToUpper(lower[0]) + lower.Substring(1); // zmiana nazwy tak, aby pierwszy znak był wielki, a pozostałe małe
}
Warto zauważyć, że zmieniona została nazwa kolumny, ale kolumna źródłowa pozostała taka sama, co może mieć znaczenie dla źródła danych:
Dodanie przedrostka do nazwy kolumny
Na poprzednim przykładzie widać jednak, że nazwy kolumn w obu tabelach są identyczne. W takiej sytuacji warto rozważyć dodanie przedrostka do nazwy kolumny, by łatwiej było ją później zidentyfikować. Tu również z pomocą przychodzi skrypt C#, z którym zrobimy to w kilka chwil.
Do kolumn w tabeli „County_destination” dodamy przedrostek z nazwą tabeli. Jednak by kolumna nadal wyglądała ładnie dla naszych użytkowników, w translacji znak _ zastąpiony będzie spacją.
Do kolumn w tabeli „County_origin” również dodamy przedrostek „Origin_” i tu również w translacji zastąpimy _ spacją.
Dokonując zmian nazw obiektów, musimy pamiętać, że jeśli do zmienianych przez nas kolumn odwołują się raporty albo inne obiekty w modelu, to relacje te zostaną zniszczone, a obiekty i raporty będą zgłaszać błędy. Dlatego tego typu operacje należy przeprowadzać po wcześniejszym upewnieniu się, jakie będą ich konsekwencje.
Zaczniemy od prostszej wersji w tabeli County_origin, dodając jednocześnie opis pola:
foreach (var col in Selected.Columns)
{
col.Name = "Origin_"+col.Name ; //zmiana nazwy kolumny przeprzedzając ją stałym tekstem oraz _
col.TranslatedNames["en-US"] = col.Name.Replace("_", " "); //ustawienie nazwy w translacji jako nowej nazwy kolumny, z _ zamienionym na spację
col.Description = "Identifying "+col.TranslatedNames["en-US"]; //ustawienie opisu pola
}
Pola przed zmianą:
Po zmianie:
Dla tabeli County_destination jako przedrostka nazwy kolumn możemy użyć nazwy tabeli. Wykonamy bardzo podobne operacje, ale nasze rozszerzenie nazwy nie będzie stałym tekstem, ale dynamiczną nazwą tabeli:
foreach (var col in Selected.Columns)
{
col.Name = Selected.Table.Name +"_"+col.Name ; //zmiana nazwy kolumny przeprzedzając ją nazwą tabeli oraz _
col.TranslatedNames["en-US"] = col.Name.Replace("_", " "); //ustawienie nazwy w translacji jako nowej nazwy kolumny, z _ zamienionym na spację
col.Description = "Identifying "+col.TranslatedNames["en-US"]; //ustawienie opisu pola
}
Pola przed zmianą:
Pola po zamianie:
Dodanie miar na podstawie istniejących kolumn
Kiedy przygotowaliśmy już kolumny w naszym modelu, warto przygotować również miary bazujące na kolumnach, no sumy, średnie itp.
Poniższy skrypt pozwoli nam przygotować sumy wybranych kolumn, jednocześnie nadając im odpowiednie formatowanie i opis, a także umieści je w nowym folderze oraz ukryje kolumnę bazową w modelu:
c – kolumna bazowa, newMeasure – nowa miara bazująca na kolumnie bazowej c
foreach(var c in Selected.Columns)
{
var newMeasure = c.Table.AddMeasure(
c.Name+"_sum", // Nazwa nowej miary
"SUM(" + c.DaxObjectFullName + ")" // DAX expression
);
// ukrycie kolumny, na której bazujemy miarę:
c.IsHidden = true;
//ustawienie formatowania nowej miary:
newMeasure.FormatString = "0.00";
// ustawienie Display Folder dla nowych miar:
newMeasure.DisplayFolder = "Measures";
//ustawienie opisu nowej miary:
newMeasure.Description = "Sum of column " + c.DaxObjectFullName;
}
Po uruchomieniu skryptu zostają utworzone 3 nowe miary w nowym folderze „Measures”, a ich formatowanie i opis będą zgodne z konwencją, którą zdefiniowaliśmy. Kolumny bazowe zostały ukryte:
Dodawanie obiektów zdefiniowanych w osobnym pliku
Miary i kolumny tworzone w SSAS nie zawsze są tak proste i powtarzalne. Czasem trzeba w nich zaszyć dodatkową logikę, którą łatwiej zdefiniować w innym narzędziu, np. Excelu. W sytuacji, gdy takich obiektów jest dużo, prostszą metodą ich stworzenia będzie użycie skryptu wczytującego plik z definicją zamiast ręcznego przeklejania. Eliminujemy wówczas ryzyko zwykłego ludzkiego błędu.
Tabular editor oczekuje od nas pliku w formacie tsv rozdzielanego tabulatorami. Przygotować taki plik możemy np. w Excelu.
Przygotowanie pliku:
- Zapisujemy nazwę kolumny, jej opis oraz definicję w kolumnach A, B i C. Kolejne wiersze to kolejne kolumny w modelu:
- Zapisujemy sheet jako plik txt rozdzielany tabulatorami:
- Następnie we właściwościach pliku ręcznie zmieniamy rozszerzenie pliku na .tsv:
Pamiętaj, aby zamknąć plik, zanim zaakceptujesz zmianę!
Plik ze zmienionym rozszerzeniem zawsze możemy otworzyć w Notepad++
W poniższym skrypcie dodajemy nowe kolumny oraz miary na nich bazujące:
var targetTable = Model.Tables["Data"]; // Nazwa tabeli, do której dodajemy kolumny
var measureMetadata = ReadFile(@"c:\Users\kwyderka\Documents\InputForArticle\MeasureDefinition.tsv"); // Przygotowany wcześniej plik tsv
var tsvRows = measureMetadata.Split(new[] {'\r','\n'},StringSplitOptions.RemoveEmptyEntries); // podział pliku tsv na wiersze
// Pętla idąca po wierszach, z których każdy definiuje jedną kolumnę
foreach(var row in tsvRows.Skip(1)) //pomijamy pierwszy wiersz z nagłówkiem
{
var tsvColumns = row.Split('\t'); // określamy, że znakiem rozdzielającym kolumny jest tabulator
var name = tsvColumns[0]; // Kolumna nr 1 - Nazwa kolumny
var description = tsvColumns[1]; // Kolumna nr 2 - Opis kolumny
var expression = tsvColumns[2]; // Kolumna nr 3 - Definicja kolumny
var folder = tsvColumns[3]; // Kolumna nr 4 - Folder, do którego kolumna zostanie dodana
//dodajemy kolumnę o określonej nazwie do wybranej tabeli oraz dodajemy jej właściwości
var col = targetTable.AddCalculatedColumn(name);
col.Description = description;
col.Expression = expression;
col.DisplayFolder=folder;
//na podstawie dodanej kolumny tworzymy równiez miarę, definiowaną jako sumę tej kolumny
var measure = targetTable.AddMeasure(col.Name+"_sum", // Nazwa nowej miary
"SUM(" + col.DaxObjectFullName + ")" // DAX expression
);
measure.DisplayFolder="Measures";
}
Po uruchomieniu skryptu kolumny opisane w pliku tsv oraz bazujące na nich miary zostały dodane do naszego modelu:
Zapisywanie zmian w pliku pbix
Dopóki nie zapiszemy zmian w Tabular Editor, dokonane przez nas zmiany nie będą widoczne w programie Power BI Desktop i znikną po zamknięciu narzędzia. Wyrażenia wymagające przeliczenia modelu (kolumny kalkulowane, miary) oznaczone będą jako wymagające deplomentu/walidacji:
By zapisać zmiany w połączonej bazie – w naszym przypadku w pliku pbix – wystarczy użyć opcji File -> Save:
Po zapisaniu wyrażenia zostaną zwalidowane, a w przypadku błędów, zostaną one oznaczone. Natomiast w pliku pbix otworzonym w PowerBI Desktop zobaczymy komunikat o konieczności odświeżenia (przeliczenia) modelu, w związku z nowymi kolumnami, które stworzyliśmy w Tabular Editorze:
Po odświeżeniu modelu kolumny stają się gotowe do użycia, a nowe nazwy, miary i inne zmiany obecne są w pliku otwartym w Power BI Desktop.
Pamiętaj, że po przeliczeniu zmiany należy zapisać również w pliku pbix w Power BI Desktop!
Podobnie dzieje się, jeśli pracujemy na modelu SSAS otwartym np. z Power BI Workspace – po zapisaniu zmian w Tabular Editor stają się one widoczne w naszym modelu, ale część z nich może wymagać ręcznego przeliczenia (odświeżenia), by stały się dostępne do użycia w raportach.
Data & Analytics
Dzięki naszym usługom analizy i przetwarzania danych będziesz podejmować trafne decyzje, zbudujesz skuteczne strategie i znajdziesz nowe źródła przychodów.
Oferta Data&AnalyticsPodsumowanie
Wykorzystanie Advanced Scripting w Tabular Editorze daje ogromne możliwości usprawnienia i zautomatyzowania pracy z modelami tabularycznymi. Powyższy artykuł prezentuje jedynie wycinek możliwości tego narzędzia, a mimo to pozwala zaoszczędzić wiele nudnych godzin na przeklejaniu i przepisywaniu kodu miar, kolumn, nazw, formatowania czy translacji.
Niewątpliwą zaletą tego rozwiązania jest możliwość wykorzystania Tabular Editora do edycji modeli dostępnych wyłącznie w Power BI Desktop. Sprawia to, że z usprawnień skorzystać może niemal każdy użytkownik Power BI – nawet niemający dostępu do środowiska Premium czy infrastruktury serwerowej.
Automatyzacja pracy to nie tylko oszczędność czasu. To także ograniczenie wielu błędów wynikających z wykonywania manualnej, powtarzalnej pracy. Dzięki temu możemy poświęcić więcej uwagi zadaniom, które wymagają naszej wiedzy i kreatywności.
Nie musimy przeklejać tego samego kodu po raz setny – może zrobić to za nas skrypt, który wykona zadanie szybciej, dokładniej i bezbłędnie. A dla nas oznacza to mniej frustracji i być może znalezienie chwili na spokojną kawę, zanim weźmiemy się za kolejny model 😊
Zostaw komentarz