7. července 2015

MDX tutorial 4 – Aplikace MDX v SSRS

V minulých dílech tutorialu jsme si ukázali, jak si napsat svůj vlastní MDX dotaz. Stejně tak jsme si ukázali, jak pracovat s funkcí currentmember a tvořit vlastní počítané členy (článek: http://www.neoral.cz/2015/05/mdx-tutorial-3-pocitane-cleny.html). Aby naše snažení přineslo i nějaké hmatatelné ovoce, ukažme si jak vlastní dotaz dostat do reportu v SSRS (SQL Server Reporting Services). Některé věci je lepší jednou ukázat, než se snažit psát dlouhé popisy. Tvorba reportu je jednou z nich. Celý proces jsem zaznamenal se slovním komentářem a můžete jej sledovat zde https://youtu.be/RFX84weMqA8.

Video není jen o MDX, ale ukazuji i pár tipů a triků pro práci s parametry v Reporting Services. Při nahrávání jsem zapomněl namapovat do hlavního dotazu parametr SUBCATEGORY. Princip je stejný jako u parametru CATEGORY a nedostatek jsem napravil alespoň v textu článku.
Pojďme tedy tvořit report z kostky. Výsledkem našeho snažení bude report analýzy prodejů v čase s parametry Kategorie a Podkategorie produktů.
Co budeme potřebovat? V prvé řadě dataset pro hlavní report. 
Zdrojový select pro hlavní dataset bez parametru vypadá:
SELECT
       {[Measures].[Reseller Sales]
       ,[Measures].[Internet Sales]}
ON COLUMNS,
NON EMPTY
       {[Dim Date].[Year].[Year]}*
       [Dim Date].[Quarter].[Quarter]*
       [Dim Date].[Month].[Month]
ON ROWS
FROM [MDX Tutorial]
Pokud chceme dotaz parametrizovat, narazíme na dva problémy. Zaprvé MDX jazyk nezná nic jako deklaraci proměnných.  Zadruhé Reporting Services budou hodnotu parametru podsouvat do SSAS zdroje jako text. Textovou hodnotu můžeme převést na člena funkcí StrToMember nebo na množinu prvků funkcí StrToSet. Pro parametry většinou použijete funkci StrToSet. Složitější je řešení problému s chybějící deklarací. Pokud se na parametr odvolávám, nejdou mi načíst pole v designeru, takže si chystám dataset tak aby se dal ladit v Management Studiu. Nachystám si něco jako defaultní filter a parametr ve funkci StrToSet dočasně zakomentuji. Hodnoty parametrů se zpravidla píšou do Subselectů kvůli omezení, že jedna hierarchie v MDX může figurovat pouze na jedné ose.
Spustitelný dotaz z SSMS (SQL Server Management Studio) kopíruji do schránky
SELECT
       {[Measures].[Reseller Sales]
       ,[Measures].[Internet Sales]}
ON COLUMNS,
non empty
       {[Dim Date].[Year].[Year]}*
       [Dim Date].[Quarter].[Quarter]*
       [Dim Date].[Month].[Month]
ON ROWS
FROM
(SELECT [Dim Product].[Category].&[Bikes]/*StrToSet(@CATEGORY)*/ ON COLUMNS FROM
[MDX Tutorial]
)
pravé tlačítko na datový zdroj Analysis Services, query designer (přímo psát nejde), přepnutí ikonkou s trojúhelníkem z design módu do textového, náhrada dotazu mým skriptem ve schránce, ok. To by mělo načíst seznam polí na záložce fields. Tlačítko Fx mě dostane do dotazu v textovém režimu, kde můžu umazat komentáře a získat dotaz
SELECT
       {[Measures].[Reseller Sales]
       ,[Measures].[Internet Sales]}
ON COLUMNS,
non empty
       {[Dim Date].[Year].[Year]}*
       [Dim Date].[Quarter].[Quarter]*
       [Dim Date].[Month].[Month]
ON ROWS
FROM
(SELECT StrToSet(@CATEGORY) ON COLUMNS FROM
(SELECT StrToSet(@SUBCATEGORY) ON COLUMNS FROM
[MDX Tutorial]
)
)
Parametry CATEGORY a SUBCATEGORY musím vytvořit manuálně. Zbývá napsat dva datasety, kterými budu parametry plnit.
Syntaxe datasetu plnící jednopoložkový  parametr CATEGORY by mohla vypadat následovně
WITH MEMBER ParameterValue AS
       [Dim Product].[Category].currentmember.uniquename
MEMBER ParameterLabel AS
       [Dim Product].[Category].currentmember.name
SELECT
       {ParameterValue
       ,ParameterLabel}
ON COLUMNS,
nonempty(
       {[Dim Product].[Category].members}
       ,{[Measures].[Reseller Sales],[Measures].[Internet Sales]}
       )
ON ROWS
FROM [MDX Tutorial]

Počítaný člen ParameterValue vrací unikátní identifikaci každého záznamu, ParameterLabel se odkazuje na name column, zde by se dalo použít i MEMBER_CAPTION což je jméno po aplikaci translations pokud v kostce existují. Výraz na řádcích vratí všechny členy atributové hierarchie včetně All membera, ale pouze ty kategorie, které se prodávaly.
Dataset pro parametr SUBCATEGORY je ekvivalentní, jen s odkazem na předchozí parametr.
WITH MEMBER ParameterValue AS
       [Dim Product].[Subcategory].currentmember.uniquename
MEMBER ParameterLabel AS
       [Dim Product].[Subcategory].currentmember.name
SELECT
       {ParameterValue
       ,ParameterLabel}
ON COLUMNS,
nonempty(
       {[Dim Product].[Subcategory].[Subcategory]}
       ,{[Measures].[Reseller Sales],[Measures].[Internet Sales]}
       )
ON ROWS
FROM [MDX Tutorial]
WHERE StrToSet(@CATEGORY)
Hotový report si můžete stáhnout zde
Závěr:

Článek shrnuje tvorbu parametrizovaného Reporting Services reportu vytvořéného nad daty z OLAP kostky, pro nějž jsme si napsali všechny dataset. Všechny kouzla jsou vidět na videu, článek slouží spíš jako doplněk kvůli dohledání „jak ten skript vypadal“

2 komentáře:

  1. zdravim, mam dataset, kde si vybere z casove dimenze roky. To vse nactu do parametru v reportu. Dataset mi vrati jednotlive roky 2010,2011,2012,.. Ja potrebuju pridat jeste jednu hodnotu a to "n/a" a te nastavit rok 9999. Jedna se o parametr, kde se vybiram rok ke srovnani. Hodnota "n/a" slouzi k tomu, aby se vyjely jen aktualni data bez porovnani se zadanym. Muj MXD dotaz vypada zatim nasledovne

    WITH MEMBER ParameterValue AS
    [Time].[Rok].currentmember.uniquename
    MEMBER ParameterLabel AS
    [Time].[Rok].currentmember.name
    SELECT
    {ParameterValue
    ,ParameterLabel}
    ON COLUMNS,
    nonempty(
    {[Time].[Rok].members - [Time].[Rok].[all]}
    ,{[Measures].[Platba]}
    )
    ON ROWS
    FROM [Masek Pohledavky]

    dekuji za pomoc

    OdpovědětSmazat
  2. Zdravim,
    omlouvam se za pozdni reakci, nejak mi notifikace zapadla v poste
    WITH MEMBER ParameterValue AS
    [Time].[Rok].currentmember.uniquename
    MEMBER ParameterLabel AS
    [Time].[Rok].currentmember.name
    MEMBER [Time].[Rok].[all].[N/A] AS
    9999
    SELECT
    {ParameterValue
    ,ParameterLabel}
    ON COLUMNS,
    nonempty(
    {[Time].[Rok].allmembers - [Time].[Rok].[all]}
    ,{[Measures].[Platba]}
    )
    ON ROWS
    FROM [Masek Pohledavky]
    By snad melo fungovat

    JN

    OdpovědětSmazat