SQL Server Analysis Services dimensioner

af Joakim Dalby

 

En række eksempler på dimension implementationer sammen med tips om hurtigere performance.

Unified Dimensional Model (UDM) giver en række forskellige dimensioner som præsenteres her.

 

Person data som Niveau dimensioner

En liste over personer med en række forskellige data, som ikke er på 3. normalform, men derimod klar til en række OLAP (OnLine Analytical Processing for multidimensional data analysis) kube dimensioner. Listen er enten et view fra en relations­databa­se der joiner en række tabeller sammen, eller en tabel fra en datamart i et datawarehouse miljø:

 

Dim_Per-son_Id

Cprnr

Navn

Lands-del_Id

Landsdel

Aldergrup-pe_Id

Alders-gruppe

Køn_Id

Køn

1

090880-3019

Ib Pihl

100

Sjælland

2

20-50

1

Mand

2

231150-0387

Per Toft

300

Jylland

3

51-75

1

Mand

3

070206-5410

Pia Gro

200

Fyn

1

0-19

2

Kvinde

4

150182-0152

Maj Pihl

100

Sjælland

2

20-50

2

Kvinde

 

Dim_Person_Id er dannet af datawarehouse som en »surrogate key« d.v.s. et fortløbende nummer uden be­tyd­ning og anvendes som dimensionsreferencen i fact tabellen, mens Landsdel_Id og Køn_Id i datawarehouse er defineret af brugerne til styring af sorteringen af de tilhørende tekster. Cprnr er en primærnøgle fra OLTP (OnLine Transaction Pro­ces­sing) kildesystem, kaldet »business key«, men det er ikke godt at anvende en tekst-baseret primærnøgle i en di­men­sion eller andre »business key«, fordi de kan ændre sig i kildesystemet. Når Id anvendes i dimensionen er der større uafhængighed til kildesystemet, og Id nummeret ændrer sig ikke, hvilket er praktisk, når ana­ly­se-rapporteringsværktøjet husker på filtreret dimensionsværdier, også kaldet zoom ind på bestemte værdier.

 

Listen kan eksempelvis give en række dimensioner i olap kuben, hvor de tre første er niveau dimensioner med hierarki på to niveauer, og de sidste dimensioner er baseret på attributterne CprnrNavn, Cprnr og Navn:

 

Landsdele og personer

Aldersgrupper og personer

Køn og personer

Personer som selvstændig

Kun personernes Cprnr

Kun personernes Navn

 

 

I SQL Server Business Intelligence Development Studio i et Analysis Services projekt implementeres olap kube med dimensioner og measuregroup, hvor det anbefales at anvende views som grænsefladen (interface) mel­lem ovenstående liste og olap kuben, og det view dannes i SQL Server Management Studio, hvor CprnrNavn er en sammensætning af Cprnr og Navn felterne:

 

 

Person dimensionen implementeres via en wizard, hvor dimensionsreferencen Dim_Person_Id til fact tabellen væl­ges som Key og CprnrNavn som Name, d.v.s. det er CprnrNavn oplysningen der vises til brugeren i analy­se-rapporteringsværktøjet fremfor Id numme­ret:

 

 

I Wizard sættes Id-kode og værdi sammen som Key og Name, og Id-kode attributterne skjules ved at fjerne de­res flueben:

 

 

Det giver følgende egenskaber for eksempelvis Landsdel attributten, hvor KeyColumns peger på Id for Landsdel der både bliver anvendt i et kommende hierarki og til at sortere landsdelene på, hvor NameColumn peger på teksten for Landsdel, som bliver vist til brugeren i analyse-rapporteringsværktøjet. ValueColumn beskrives til sidst i dette tip. KeyColumns er i flertal, fordi den kan indeholde en sammensætning af to eller flere felter, der tilsammen udgør en unik identifikation af data.

 

 

Niveau hierarkier opbygges med attributten DIM Person nederst, fordi den er referencen til fact data via Key Dim_Person_Id, og derfor behøver attributten ikke være tilgængelig, så den skjules i Attribute­Hierarchy­Visible:

 

 

Niveau DIM Person i de tre niveau hierarkier skal have ændret sit Navn til det der ønskes vist til brugeren, når dimen­sio­nen anvendes og der foretages drill-down til Person niveauet. Ændringen gøres i Name egenskaben:

 

 

De enkelte niveauer bibeholdes som selvstændige viste attributter og kan derfor anvendes af brugeren efter behov i analyse-rapporteringsværktøjet.

 

Det er vigtigt at markere øverste node i attribut træet, d.v.s. dimensionsnavnet, ovenfor er det Person, og æn­dre ErrorConfiguration fra (default) til (custom) og klikke på + tegnet der åbner for at sætte egenskaber, så fejl ikke bliver ignoreret og glemt, men at fejl bliver rapporteret og stopper kube processering:

 

 

 

Som standard er KeyErrorLimit 0, d.v.s. der stoppes efter første fejl selvom der er valgt ReportAndContinue, fordi KeyErrorLimit kunne ændres til eksempelvis 9. Der findes også en ReportAndStop som altid stopper.

 

Niveau dimension (Level based dimension, Regular Dimension Relationships)

En kalender er et klassisk eksempel på flere niveauer inden for en dimension. Et view udsøger data fra År til Må­ned, hvor hvert niveau har en kode og en værdi, undtagen År da den både er kode og værdi:

 

 

I Wizard sættes referencen til fact data ÅrMåned som Key og Måned som Name:

 

 

I Wizard sættes kode og værdi sammen som Key og Name, og kode attributterne skjules ved at fjerne de­res flueben:

 

 

Det er vigtigt at trække forrige niveau ind på »new attribute relationship«, og når År, Halvår og Kvartal har ko­de som key d.v.s. et heltal, så bliver aggregeringen optimal. Niveau hierarki »År – måned« (»user-defined hi­erar­chy«) op­byg­ges med attributten DIM TID MÅNED nederst, fordi den er referencen til fact data. Det bli­ver alt i alt til følgende attribut kæde: DIM TID MÅNED -> Kvartal -> Halvår -> År. Ved markering af hierarki overskrif­ten kan en række egenskaber angives: Hierarki navnet i Name, top teksten »Alle« i AllMemberName, og normalt har et niveau (mor) unikke under­ni­ve­a­uer (børn) hvilket angives med False i AllowDuplicateNames, hvorved processeringen vil fejle hvis det utænkelige opstår, at eksempelvis Januar forekommer to gange under samme kvartal i samme halvår og år. Værdien Januar gentages under forskellige år, men koden i ÅrMåned er forskellig (200001 eller 200101), derfor sættes MemberNamesUnique til False. Havde værdien i Halvår, Kvar­tal og Måned været efterfulgt af årstal (Januar 2000, Januar 2001) kunne MemberNamesUnique sættes til  True og opnå en performance fordel. MemberKeysUnique er NotUnique fordi koden ikke er unik på tværs af ni­ve­au­er eksempelvis er koden 20001 anvendt både for Halvår og Kvartal.

 

 

En kort visning af nogle af de mange andre egenskaber som opsættes for dimensionen. Det er vigtigt at gen­nem­se alle egenskaberne ved at klikke på alle elementerne i Dimension Structure vinduet, og evt. tilrette nog­le af egenskaberne:

 

 

 

Når en dimension kun bliver processeret i forbindelse med »full processing«, opnås en performance ved at sæt­­te attribute relationship egenskabens RelationshipType til Rigid. Når en dimension løbende bliver »incremen­tal update processing« skal RelationshipType sættes til Flexible, selvom det giver en warning i AS2008.

 

 

 

Fra viewet er År både kode (key) og værdi (name), så derfor angives der ikke noget i NameColumn:

 

 

Niveau DIM TID MÅNED skal have ændret sit navn til det der ønskes vist til brugeren, når dimen­sio­nen anvendes og der foretages drill-down til Måned niveauet. (I Data Source View er ÅrMåned feltet blevet gjort til lo­gisk primærnøgle, så dimensionen kan indgå i et star schema til en fact tabel, herom senere, derfor vises en nøgle ikon ved ÅrMåned feltet). Den logiske primærnøgle sættes ud fra clustered index i fact tabellen.

 

 

Attribut DIM TID MÅNED bibeholder sit navn for at vise den senere i forbindelse med drill through, men det an­be­fales at alle attributter omdøbes til sigende navne. Her kunne DIM TID MÅNED været blevet omdøbt til Må­ned.

 

Dimensionen efter processering. Navigeringen (»navigation path«) er fra Alle år, til et år, til et halvår, et kvar­tal, og til sidst vises kvartalets tre måneder, d.v.s. drill down fra År til Måned:

 

 

Man kan sige, at der er tale om en sammensat dimension af fire selvstændige elementer: År, Halvår, Kvartal og Måned, og at samme viste dimensionsværdi f.eks. månedsnavne går igen under forskellige kvartaler i forskellige halvår og i forskellige år. Ønsker man at kunne vise en sum af alle Januar månederne uanset år, må der i viewet tilføjes en ny kolonne Månedsnummer som indeholder 1..12 og der dannes en attribut Måned­Uden­År med Må­neds­nummer kolonnen som Key og Måned kolonnen som Name.

 

MorBarn dimension (Parent Child based dimension, Parent-Child Dimension Relationships)

Økonomiske dimensioner er ofte MorBarn dimensioner, men for at vise forskellen i implementationen til Niveau dimension anvendes kalenderen igen med et view der udsøger data med kode, værdi og sidstekode (parent):

 

 

I Data Source View skal den rekursive relation fremgå ved MorBarn dimension til den senere wizard:

 

 

Det er parent feltet som vil danne MorBarn hierarkiet, derfor opsættes følgende i wizard:

 

 

 

Bemærk, at attributten Parent Period har en hierarki ikon, derfor skal der ikke opbygges et hierarki. Egen­ska­ber­ne til­ret­tes:

 

 

 

Attributten Parent Period navn ændres til det, der ønskes vist ved brug af dimensionen og niveauerne i MorBarn dimensionen tildeles egentlige navne:

 

 

Rollespil dimension (Role-Playing Dimension Relationships)

Når en fact tabel anvender flere dimensioner med samme indhold, siger man, at dimensionen spiller flere roller mod fact tabellen, eksempelvis en Ordre fact tabel med dimensioerne Ordredato, Leveringsdato, Betalings­dato, hvor År-Dato dimensionen spiller tre roller for h.h.v. Ordre, Levering og Betaling. Rollespil dimensionen har flere forskellige navne inden for samme kube og measuregroups, idet en dimension kan genbruges i flere kuber og flere measuregroups og også inden for samme measuregroup med hvert sit dimensionsnavn.

 

Her vises et eksempel på en fact tabel over personers månedlige til- og fraflytninger og i Data Source View op­stil­les et Star Schema, hvor fact tabellen har to referencer til DIM_TID_MÅNED dimensionen fordi der spilles to roller for h.h.v. Tilflytning og Fraflytning. Fact tabellen har ikke nogen oplysning til analysevariabel og kaldes »factless fact table«, så analysevariablen bliver en tælling af rækker (Count):

 

 

Med Cube Wizard foretages forskellige valg, idet fact tabellen ikke har nogen analysevariabel anvendes en Count på selve fact tabellens rækker for at få beregnet antallet af flytninger:

 

 

 

 

 

Der fås en Kube kaldet Flytning indeholdende en MeasureGroup kaldet FACT FLYTNING som anvender fact tabellen FACT_FLYTNING samt en række­tæl­ler (Count) analysevariabel kaldet Flytninger:

 

 

Under fanen Dimension Usage ses det, at Tid måned dimensionen spiller to roller i samme measuregroup, og hver rolle kan navngives til det dimensionsnavn brugeren vil have vist i analyse rapporteringsværktøjet:

 

 

Et eksempel på en analyse opstilling, hvor der vises de personer af alle køn som er tilflyttet i år 2007 og fraflyttet i enten år 2007 eller år 2008 vist på månedsniveau. Til Flyt dimensionen er placeret som Filter dimension med zoom på 2007. Fra Flyt dimensionen er placeret som Kolonne dimension med zoom på 2007 og 2008 og drill-down til måneder. I Række forspalten er personerne inddelt i Landsdel og Aldersgruppe, hvor Al­ders­gruppe er hierarkiet med personer vist i drill-down, og Landsdel er attribut fra Person dimensionen. Køn attributten fra Person dimensionen står på Alle, men ændres filteret til Mand, så vises kun flytninger blandt de mandlige personer. Ligeså kunne Landsdel, Aldersgruppe eller Alder attributterne placeres som filter med forskellige zooms afhængig af de forskellige analyser der ønskes. Fact tabellen har ikke oplysning om Landsdel, Alder og Køn, idet fact tabellen kun anvender Dim_Person_Id. Men i Person tabellen beriges Dim_Per­son_Id med ekstra oplysninger, og når de indgår i Person dimensionen kan de anvendes i analyse rapporteringen mod fact tabellen.

 

 

Reference dimension (Reference Dimension Relationships)

Normalt har fact tabellen et antal dimensionsreferencer, som Id’er til dimensionsdata. Men en dimension behø­ver ikke at have en direkte relation til en fact tabel, hvis dimensionen i stedet har en re­fe­ren­ce til en anden dimension, der indgår i fact data. Fordelen er, at olap kuben kan blive beriget af dimensioner, som ikke er kendt af fact data og kildesystemerne.

 

Data Source View er blevet udvidet med en Politik dimension, som har en reference til Person dimensionen (en-til-mange relelation) men ikke til fact tabellen FACT_FLYTNING. Politik dimensionen indgår en anden fact tabel  FACT_STEM­MER, der indeholder antallet af stemmer på et parti i en givet måned.

 

 

DSV indeholder to Star schema, hvilket også kaldes et Constellation schema der består af flere dimensioner  og flere fact data tabeller som giver flere measuregroups i en kube med fælles dimensioner (shared dimensi­ons). Bilagsnummer anvendes senere til drill through via en fact dimension.

 

I Person dimensionen sættes en række egenskaber på attributten Dim Person Id, så den ikke belaster di­men­si­o­nen:

 

 

Politik dimension opbygges med et hierarki og sigende attributnavne, hvis Keys peger på Id’erne (Parti attributten er en omdøbning af DIM POLITIK):

 

 

Ny measuregroup tilføjes og eksterende measuregroup tildeles et sigende navn samt en analysevaribel der summerer stemmer:

 

 

I Dimension Usage adderes Politik dimensionen og der angives en reference mellem Politik via Person til measu­re­group Flytning som anvender FACT_FLYTNING fact tabellen, mens til Vælgertilslutning er det en normal regular dimension, fordi FACT_STEMMER indeholder Dim_Politik_Id dimensionsreferencen:

 

 

Intermediate betyder, at der er en direkte forbindelse mellem dimension og measuregroup.

Her er Person dimensionen den mellemliggende dimension mellem Parti dimensionen og measuregroup Flytning.

I Dimension Usage illustreres reference dimension med ikonen:

 

Politik dimensionen kan eksempelvis anvendes som række forspalte dimension mod Flytninger data, fordi olap kuben lader forbindelsen gå gennem Person dimensionen, som ikke behøver at indgå i analysen:

 

 

Samme Politik dimension anvendes også mod Vælgertilslutningsdata:

 

 

Fact dimension (Degenerate Dimension Relationships)

Det anbefales ikke i kube implementationen at danne dimensionsværdier baseret på fact tabellens indhold, for­di der sjæl­dent fås en Id til Key og at det koster i svartid på kube processeringen. Men i forbindelse med drill through til en measuregroup kan fact dimension være anvendelig, fordi drill through kun kan ses på di­men­­sio­ner. Når et bilagsnummer ønskes vist i drill through, skal der dannes en dimension indeholdende alle bilagsnumre og når fact tabellen har et LoadId, kan den indgå som Key og Bilagsnummer som Name. En fact dimension vil altid være en en-til-en relation til fact tabellen, og en fact dimension kan kun indgå i en measu­re­group. Fact dimension for Bilagsnummer implementeres:

 

 

 

 

I Dimension Usage illustreres fact dimension med ikonen:

 

Drill through

Drillthrough implementeres som en action, hvor der inden for hver dimension vælges de niveauer der ønskes vist. Her ses det, at attributnavne bør være sigende og ikke DIM TID MÅNED, når der menes Måned:

 

 

Action navnet tilsidesættes med Caption som kan gøres dynamisk med MDX typisk CurrentMember, så der fås et godt navn i analyse-rapporteringsværktøjet, når der højreklikkes i en cellen og en menu fremkommer.

 

Drill through kolonne-overskrifter er ikke så sigende fra Microsoft og niveau-rækkefølgen inden for en di­men­si­on vises alfabetisk f.eks. Parti og Placering fremfor i hierarkiet Placering -> Parti:

 

 

I Drill through action kan der implementeres den MDX sætning som ønskes sendt til analyse-rap­por­terings­værk­tøjet, hvor kolonne-overskrifter kan omdøbes og placeres i ønsket rækkefølge:

 

 

 

Drillthrough Columns

Er man træt af, at rækkefølgen af dimensioner i en Action drill through ikke kan ændres i SQL Server Business Intelligence Development Studio, så kan det tilrettes i kubens .cube fil under projekt-mappen ved at åbne den i Notepad og gå i bunden af filen til:

<Actions>

  <Action xsi:type="DrillThroughAction" dwd:design-time-name="3a396708">

og bytte rundt på Column xml tags, bemærk dog, at en dimension kan have to eller flere Columns, når drill through skal vise flere kolonner fra dimensionen, eksempelvis:

        <Column xsi:type="CubeAttributeBinding" dwd:design-time-name="ca5b8ef1-2164-4313">

          <CubeID>Kunde</CubeID>

          <CubeDimensionID>År Dato Uge</CubeDimensionID>

          <AttributeID>Uge Start År</AttributeID>

          <Type>All</Type>

        </Column>

        <Column xsi:type="CubeAttributeBinding" dwd:design-time-name="ec60f012-ed99">

          <CubeID>Kunde</CubeID>

          <CubeDimensionID>År Dato Uge</CubeDimensionID>

          <AttributeID>Uge</AttributeID>

          <Type>All</Type>

        </Column>

 

Alternativ metode til en nem opsætning af Drillthrough gennem en komponent

https://asstoredprocedures.codeplex.com/

 

Mange-til-mange dimension (Many-To-Many Dimension Relationship, multiple-valued dimension)

Det normale er en en-til-mange relation, hvor fact data rækken refererer til en dimensionsværdi i samme di­men­si­on, men med mange-til-mange relation muliggøres det, at en fact da­ta række refererer til flere dimensionsværdier i samme dimension

 

I AdventureWorksDW er tabel InternetSalesReason bindeleddet mellem dimensionstabel SalesReason og fact tabel InternetSales, hvori det håndteres, at en SalesOrder kan skyldes flere Reasons, d.v.s. mange-til-mange relation mellem Salgsordre og Grund. En salgsordre kan skyldes flere grunde, f.eks. at kunden har modtaget en reklame, set en demonstration eller fået en anbefaling fra en ven. I en analyse ønskes det, at samme salgs­beløb kan ses for hver af de grunde som skyldes salgsordren, d.v.s. en gentagelse af beløbet selv­om det kun findes en gang i fact tabellen. Implementeringen foretages med en mange-til-mange relation mellem SalesReason og InternetSales via InternetSalesReason, hvor sidstnævnte bliver en ny measuregroup på en factless fact table, så analysevariablen bliver en rækketæller Count og skjules og får SalesReason tilføjet som regular dimension:

 

 

De to measuregroups Internet Sales og Internet Sales Reason skal have mindst en fælles dimension, derfor dan­nes på fælles felterne SalesOrderNumber+SalesOrderLineNumber en skjult Sales Order fact dimension på In­ter­net Sales og anvendes som regular dimension på Internet Sales Reason. Herefter kan many-to-many dan­nes mellem Sales Reason og Internet Sales selvom dimension og fact tabel ikke har en fælles reference, hvilket figuren illustrerer med en stiplet pil:

 

 

Intermediate betyder, at der er en direkte forbindelse mellem dimension og measuregroup.

Her er Internet Sales Reason measuregroup den mellemliggende measuregroup mellem Sales Reason dimensio­nen og measuregroup Internet Sales.

I Dimension Usage illustreres many-to-many dimension med ikonen:

 

I analysen vil samme salgsbeløb indgå i flere grunde, og derved vil totalerne ikke udgøre summen af grun­de­ne, og total summen er den ægte sum fra fact tabellen:

 

 

Summen af Other giver 31.395.773, men totalen viser 25.607.494, fordi samme salgsbeløb indgår i flere grun­de men kun medregnes en gang i totalen. Ligeså er summen af totalerne 31.996.799, men viste total sum er 29.358.677, fordi samme salgsbeløb indgår i flere grunde og disse grunde er fordelt på flere typer af grunde.

 

Ovenstående eksempel kan også bruges i forbindelse med lagerbeholdningsanalyse og beholdningsregnskab med tilgang og afgang bevægelser sammenholdt med nuværende lagerantal fordeles på uger. En dimension kaldet Bestand med indholdet: Igangværende, Tilgang og Afgang, hvor Igangværende angiver, at varen er på lageret i aktuelle uge. En vare hører til Tilgang, når varen er ny på lageret i aktuelle uge, men varen var ikke på la­ge­ret i sidste uge. En varer hører til Afgang, når varen er på lageret i aktuelle uge, men varen er ikke på lageret i næste uge. En vare der er på lageret i uge 45 men ikke på lageret i uge 46, er regnet som afgang i uge 45, og en vare der er på lageret i uge 45 men ikke på lageret i uge 44, er regnet som tilgang i uge 45. Der­ud­over er varen igangværende i uge 45, d.v.s. samme vare har i dette eksempel tre bestande i samme uge, hvlket implementeres med en ekstra fact tabel der pr. vare og pr. uge angiver en eller flere be­stan­de, d.v.s. en mange-til-mange relation mellem vare/uge og bestand, fordi en bestand naturligvis er knyttet til mange varer, da lageret indeholder mange varer. Næste eksempel bygger på en normal relationsdatabase fra bankverden.

 

En bankkunde ejer ofte flere bankkonti og typisk har et ægtepar en fælleskonto som begge ægtefæller ejer, d.v.s. mange-til-mange relation mellem kunde og konto. De enkelte konti grupperes i kontotyper såsom løn og pension. Pr. dag opgøres en saldo pr. konto, så når et ægtepar ejer samme konto, ønskes saldoen vist pr. person. På basis af et enterprise datawarehouse opstilles følgende dimensioner og facts i Data Source View Diagram:

 

 

Der indgår to fact tabeller og de har mindst en fælles dimension, nemlig Konto, så via kube wizard og tilpas­ning af navne fås:

 

 

 

Reference dimension Kontotype er automatisk blevet identificeret af wizard, og det gælder også Many-to-ma­ny relationship mellem dimension Kunde og measuregroup Saldo, så der kan opstilles følgende analyse, hvor saldoen gentages for hver ejer af en konto, fordi ægtepar Olsen begge er ejere til konto 900-545-6454, men de 15.000 indgår kun en gang i total summen selvom den vises for hver af de to ejere:

 

 

Snowflake dimension (Snowflaking Dimension Relationship by Normalization)

Snowflake dimension er en normal dimension i olap kuben, hvor dens niveauer ikke er placeret i én tabel som ved star schema, men niveauerne blivet dannet ved join af en række tabeller i processeringen af dimensio­nen, d.v.s. to eller flere tabeller danner niveauerne i dimensionen. Join kunne også have været dannet i et view til dimensionen, men her vises eksempler på tabeller som er normaliseret.

 

 

 

Snowflaking kan også være relevant ved sammenstykning af eksisterende dimensioner i olap kuben, som har en-til-mange relationer mellem sig, så der kan blive opbygget niveau hierarkier til drill down for brugerne i analy­se-rapporteringsværktøjet:

 

 

En snowflake dimension kan enten opbygges løbende ved at addere flere tabeller i Dimension Structure ved højreklik i Data Source View området og vælge menupunktet {Show tables} eller via wizard, hvor der her vises en snowflake dimension mellem Kontotype og Konto dimensionerne, så der opnås en niveau hierarki dimension TypeKonto. Det er vigtigt i wizard at starte med at vælge det laveste niveau i dimensionen som inde­holder dimensionsreferencen til fact tabellen.

 

 

 

 

 

 

Multiple joins mellem dimensionstabeller i snowflake lades sig ikke gøre, eksempelvis mellem tabellerne Person og By, hvor Person har to fremmednøgler FødeById og BopælsById til By primærnøglen ById, kunne have givet to niveau hierarkier inden for samme snowflake dimension: Fødeby -> Person og Bopælsby -> Person.

 

Writeback dimension

Writeback dimension er en normal dimension i olap kuben (kun i Enterprise Edition), hvor en bruger via et dimen­sions­opsætningsprogram on-the-fly kan ændre på dimensionens indhold uden at processere dimensionen.

 

Data Mining dimension

Data Mining dimension anvender dimensioner fra Data Mining modellen:

 

I Dimension Usage illustreres data mining dimension med ikonen:

 

Dimension default værdi – Default Member

Default værdier angivet i dimensionen og med role-name fra kubens Dimension Usage:

  StrToMember("[År].[År].&[" + Format(Now(),"yyyy") + "]")

  StrToMember("[Posteringsdato].[Posteringsdato].&[" + Format(Now()-1,"yyyyMMdd") + "]")

 

ValueColumn

NameColumn vil altid konvertere dataindholdet til en string, selvom data oprindelig er et heltal eller en dato, mens ValueColumn bibeholder datatypen, hvilket nogle analyse-rapporteringsværktøjer kan anvende i præ­sen­ta­tio­n af data og videre beregninger f.eks. Excel på en dato i ValueColumn. Derudover man MDX referere til Value­Column med currentmember.mem­bervalue. KeyColumns er til Id surrogate key, NameColumn er til data­vær­di og ValueCo­lumn kunne alternativt væ­re til business key.

 

I Tid måned dimensionen ønskes datoen for hver den første i månederne angivet i ValueColumn, hvilket sker ved først at medtage datoen i view, refresh Data Source View og addere til ValueColumn:

 

 

 

I År – Uge hierarki anvendes valuecolumn og MDX til at bestemme hvilket hierarki der anvendes i en analyse:

 

 

Er dimensionsværdierne heltal f.eks. i en Størrelse dimension, bliver heltalsværdierne konverteret til String i Name­Column, men bibeholder oprindelig datatype i ValueColumn:

 

 

WChar er en ADO datatype (DataTypeEnum adVarWChar) og svarer til nvarchar i SQL Server, men olap kuber kan anvende mange forskellige datakilder ud over SQL server, derfor benyttes ADO datatyperne.

 

Ærgeligt nok, kan ValueColumn ikke anvendes i sortering af dimensionsværdierne, da der kun kan vælges mel­lem Key og Name:

 

 

Key kan være en Id surrogate key som ikke giver mening at sortere efter, og Name er konverteret til string, så sorteringen bliver ikke i heltalsorden:

 

 

Sortering

Når der skal sorteres på en NameColumn feltet, hvis dimensionsværdi oprindelig er en anden datatype end string, må attributten tilføjes igen med nyt navn (her Sortering) og i KeyColumn anvendes feltet, da data­ty­pen bliver bibeholdt og ellers skjules attributten:

 

 

På den rigtige attribut (her Størrelse) vælges som AttributeKey og Sortering attributten:

 

 

Sorteringen af dimensionsværdier i heltal Størrelse dimensionen bliver korrekt over for brugerne:

 

 

 

År til dato (ÅTD, Year to date YTD) og Sidste år data

År til dato er altid knyttet til en Kalender eller Tid dimension, også når dimensionen har flere hierarkier, som eksemplet her viser, startende med en kalender tabel der også indeholder uge, derfor er rækkerne på dato niveau ÅrDato som heltal, d.v.s. Id i dimensionen, selvom dimensionen ikke ønsker et dato niveau:

 

 

Der designes tre hierarkier i Tidsdimensionen hvor År – Måned er et shortcut til År - Halvår - Kvartal - Måned, og År - Uge har sit eget hierarki, fordi en uge går på tværs af måneder og år:

 

 

Det kaldes Diamond-shaped relationship når to eller flere hierarkier har samme start og slut niveau. I SQL Server 2008 opstilles følgende Attribute relationships diagram:

 

 

Når en analysevariabel skal vise År til dato, oprettes der en beregnet analysevariabel, calculated measure, hvor forkortelsen ÅTD eller YTD ofte indgår i navnet. Beregningen udtrykkes via en MDX sætning, som angiver top niveauet i Tidsdimensions hierarki, således at ÅTD beregnes uanset om man anvender dimensionen på Halvår, Kvartal eller Månedsniveauet i sin analyse, f.eks. på månedsniveau ved at se ÅTD for de enkelte måneder:

 

ÅTD på en Sum measure kaldet Beløb, hvor Tidsdimensionen virker på begge hierarkier: År - Måned og

År - Halvår - Kvartal - Måned, fordi de har samme top og bund niveau:

 

SUM(PERIODSTODATE([Tid].[År - Halvår - Kvartal - Måned].[År]), [MEASURES].[Beløb])

 

Periodstodate giver en mængde (a set) som der summeres over beløbet på.

 

ÅTD på en DistinctCount measure kaldet Antal hvor der tælles unikke Id'er i fact tabellen:

 

AGGREGATE(PERIODSTODATE([Tid].[År - Halvår - Kvartal - Måned].[År]), [MEASURES].[Antal])

 

Sidste år data

Beløbet for et år siden i forhold til Tid dimensionens værdi:

(PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], 1,

 [Tid].[År - Halvår - Kvartal - Måned].CurrentMember),

 [Measures].[Beløb])

 

Og År dimensionen fra sidste år:

(PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], 1,

 [Tid].[År - Halvår - Kvartal - Måned].CurrentMember),

 [År].[År].CurrentMember.Lag(1),

 [Measures].[Beløb])

 

Hele sidste år hvor vi sætter valget i Tid dimensionen ud af kraft:

([Tid].[År - Halvår - Kvartal - Måned].[(All)].[All],

 [År].[År].CurrentMember.Lag(1),

 [Measures].[Beløb])

 

Delta beregning i forhold til forrige viste tidspunkt:

([Tid].[År - Halvår - Kvartal - Måned]. CurrentMember, [Measures].[Beløb]) –

([Tid].[År - Halvår - Kvartal - Måned]. CurrentMember.PrevMember [Measures].[Beløb])

 

Når Tid dimensionen er på Dato niveau, d.v.s. der kan angives en dato, så kan den dato være:

2016-02-29 i et skudår med 29 dage i februar. ParallelPeriod function does not support leap years.

The problem is previous year calculation of the Feb 29. Because there is no Feb 29 previous year.

Når jeg vælger datoen 2016-02-29 så skal sidste år sættes til 2015-02-28 fremfor Null.

Det kan håndteres på denne måde:

 

(UNION(

 PARALLELPERIOD([Date].[Calendar].[Year], 1, [Date].[Calendar].Currentmember),

  *(will be null if Feb 29)*

 PARALLELPERIOD([Date].[Calendar].[Year], 1, [Date].[Calendar].Currentmember.Lag(1))

  *(will be the prior year Feb 28 when above is Feb 29 )*

).item(0)  *(get the first member out of the union set)* , [Measures].[<sum-measure-name>])

 

Eksempelvis:

 

(UNION(

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember),

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember.Lag(1))

 ).item(0),

 [Measures].[Beløb])

 

(UNION(

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember),

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember.Lag(1))

 ).item(0),

 ([År].[År].CurrentMember.Lag(1),

 [Measures].[Beløb]))

 

ÅTD beløbet for et år siden i forhold til Tid dimensionens værdi:

SUM({PERIODSTODATE([Tid].[År - Halvår - Kvartal - Måned].[År],

         PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År],1,

         [Tid].[År - Halvår - Kvartal - Måned].CurrentMember))},[Measures].[Beløb])

 

Næste år data

Beløbet til næste år i forhold til Tid dimensionens værdi:

(PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], -1,

 [Tid].[År - Halvår - Kvartal - Måned].CurrentMember),

 [Measures].[Beløb])

 

(PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], -1,

 [Tid].[År - Halvår - Kvartal - Måned].CurrentMember),

 [År].[År].CurrentMember.Lead(1),

 [Measures].[Beløb])

 

Hele næste år hvor vi sætter valget i Tid dimensionen ud af kraft:

([Tid].[År - Halvår - Kvartal - Måned].[(All)].[All],

 [År].[År].CurrentMember.Lead(1),

 [Measures].[Beløb])

 

Alle data frem til og med

Summering fra begyndelsen frem til og med angivet Måned:

SUM({NULL:[Tid].[År - Halvår - Kvartal - Måned].CurrentMember}, [Measures].[Beløb])

 

Summering fra begyndelsen frem til og med angivet Måned og År dimensionen fra sidste år:

SUM({NULL:PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], 1,

                   [Tid].[År - Halvår - Kvartal - Måned].CurrentMember)},

                  ([År].[År].CurrentMember.Lag(1), [Measures].[Beløb]))

 

Summering fra begyndelsen frem til og med angivet Dato og År dimensionen fra sidste år:

SUM({NULL:UNION(

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember),

 PARALLELPERIOD([Tid].[År - Halvår - Kvartal – Måned - Dato].[År], 1,

  [Tid].[År - Halvår - Kvartal – Måned - Dato].CurrentMember.Lag(1))

 ).item(0)},

 ([År].[År].CurrentMember.Lag(1),

 [Measures].[Beløb]))

 

DistinctCount measure kaldet Antal tælling fra begyndelsen frem til og med angivet Måned og År dimen­sio­nen:

AGGREGATE({NULL:[Tid].[År - Halvår - Kvartal - Måned].CurrentMember}, [Measures].[Antal])

 

DistinctCount fra sidste år:

AGGREGATE({NULL:PARALLELPERIOD([Tid].[År - Halvår - Kvartal - Måned].[År], 1,

                     [Tid].[År - Halvår - Kvartal - Måned].CurrentMember)} *

                   {[År].[År].CurrentMember.Lag(1)}, [Measures].[Antal])

 

Procentvis fordeling eller andel i en calculated measure

Beløb procentvise fordeling på en dimension og dens værdier:

Total 200

A 100 50%

B 60  30%

C 40  20%

IIF(([Dimensionsnavn].[Hierarkinavn].[(All)].[All], [Measures].[Beløb]) = 0, NULL,

 ([Dimensionsnavn].[Hierarkinavn].CurrentMember,[Measures].[Beløb]) /

 ([Dimensionsnavn].[Hierarkinavn].[(All)].[All], [Measures].[Beløb]))

 

Fra SQL 2012 SP1 kom DIVIDE: Dividend / Divisor = <numerator> / <denominator>

DIVIDE(<numerator>, <denominator> [,<alternateresult>])

<numerator> The dividend or number to divide.

<denominator> The divisor or number to divide by.

<alternateresult> (Optional) The value returned when division by zero results in an error. When not provided, the default value is BLANK().

 

DIVIDE(([Dimensionsnavn].[Hierarkinavn].CurrentMember,[Measures].[Beløb]),

          ([Dimensionsnavn].[Hierarkinavn].[(All)].[All], [Measures].[Beløb]), NULL)

 

DIVIDE([Measures].[Europe Sales], [Measures].[US Sales], 0)

 

Gennemsnitlig måneds procentsatser beregnet på kvartal, halvår eller år i en calculated measure

Der anvendes MDX average funktion hvor der ikke behøver at være IIF([Measures].[Antal sager] = 0, NULL):

Avg(Descendants([Tid].[År - Halvår - Kvartal - Måned].CurrentMember,[Måned]),

  [Measures].[Antal gode sager]/[Measures].[Antal sager])

 

Inden for hver måned foretages en summering af alle månedens fact rækker i [Antal gode sager] og en tælling af alle månedens fact rækker i [Antal sager], og brøken udregnes for hver måned og med procent format fås en procentsats pr. måned. Når brugeren står i Tidsdimensionen på enten et kvartal, halvår eller år, foretages der en gennemsnitsberegning over de enkelte måneders procentsatser. Virker også selvom eksempelvis et kvar­tal ikke har fact rækker i alle tre måneder, for der beregnes kun gennemsnit for det antal måneder som har data i fact tabellen.

Eksempelvis fås tre brøker for januar, februar og marts måned: 1/3, 1/5 og 1/2 som giver procentsatserne 33,33%, 20,00% og 50,00%, og for første kvartal giver det en gennemsnitlig procentsats på 34,44%, nemlig 33,33% + 20,00% + 50,00% divideret med 3.

D.v.s. der sker ikke en summering af tællerne og nævnerne: 1+1+1=3/3+5+2=10 = 30.00%, som normalt.

Gennemsnittet har intet med vægtet eller vejet gennemsnit at gøre, fordi der ikke indgår nogen for­de­lings­væg­­te. I SQL beregnes de 34,44% som 0.3444 på følgende måde, hvor de to WHERE kan indeholde kri­te­rier for andre dimensionsværdier:

SELECT SUM(Brøk) / (SELECT COUNT(DISTINCT ÅrDato) FROM dbo.FACT) AS Decimaltal

FROM (

         SELECT ÅrDato, CAST(SUM(Tæller) AS float) / SUM(Nævner) AS Brøk

         FROM (

                  SELECT ÅrDato, COUNT(*) AS Tæller, 0 AS Nævner

                  FROM dbo.FACT

                  WHERE OK = 1

                  GROUP BY ÅrDato

                  UNION ALL

                  SELECT ÅrDato, 0 AS Tæller, COUNT(*) AS Nævner

                  FROM dbo.FACT

                  GROUP BY ÅrDato

                 ) T

         GROUP BY ÅrDato

        ) TT

Eller:

SELECT SUM(Brøk) / (SELECT COUNT(DISTINCT ÅrDato) FROM dbo.FACT) AS Decimaltal

FROM

(

SELECT ÅrDato, CAST(COUNT(*) AS float) / (SELECT COUNT(*) FROM dbo.FACT B

                                                                                      WHERE B.ÅrDato = A.ÅrDato) AS Brøk

FROM dbo.FACT A

WHERE OK = 1

GROUP BY ÅrDato

) T

 

Flere hierarkier i Tid i en calculated measure

Når Tid dimensionen har to forskellige hierarkier År - Måned og År – Uge som indgår i samme measuregroup og be­reg­net analysevariabel (calculated measure) kan der i MDX sondres mellem hvilket hierarki der anvendes i en analyse og derved er bestemmende for beregning. Her vises en gennemsnitlig ugentlig beregning:

CASE

    WHEN [Measures].[Sager] = 0

    THEN NULL

    WHEN [Tid].[År - Uge].CurrentMember.Level IS [Tid].[År - Uge].[Uge]

    THEN  /* På Uge niveau vises Sager analysevariablen fordi den i Fact også er på uge niveau */

        [Measures].[Sager]

    ELSE  /* Vises analysen på År niveauet i År – Uge eller i År - Måned, er Sager blevet summeret hvorved

                det ugentlige gennemsnit fås ved at dividere med det antal uger som indgår i viste niveau. */

        [Measures].[Sager] / [Measures].[Kalender_AntalUger]

END

 

Eller i en mere præcis udtryksform, hvor nævneren kommer fra hver sin fact tabel og derved egen analysevar:

CASE

    WHEN [Tid Uge].[År - Uge].CurrentMember.Level IS [Tid Uge].[År - Uge].[Uge] OR

              [Tid Uge].[År - Uge].CurrentMember.Level IS [Tid Uge].[År - Uge].[Uge start år]

    THEN

      IIF([Measures].[Antal Hverdage PrUge] = 0, NULL,

           [Measures].[Sager] / [Measures].[Antal Hverdage PrUge])

    WHEN [Tid].[År - Måned].CurrentMember.Level IS [Tid].[År - Måned].[Måned] OR

              [Tid].[År - Måned].CurrentMember.Level IS [Tid].[År - Måned].[År]

    THEN

      IIF([Measures].[Antal Hverdage PrMåned] = 0, NULL,

           [Measures].[Sager] / [Measures].[Antal Hverdage PrMåned])

END

 

[Antal Hverdage PrUge] kommer fra en fact tabel med alle datoer for mandage og 5 i antal hverdage.

[Antal Hverdage PrMåned] kommer fra en fact tabel med alle datoer for den 1. i måneden og antallet af

hver­dage i måneden, eksempelvis:

 

DW_FACT_KALENDER_AntalHverdagePrUge:

ÅrDato

AntalHverdage

20101220

5

20101227

5

20110103

5

20110110

5

 

DW_FACT_KALENDER_AntalHverdagePrMåned:

ÅrDato

AntalHverdage

20101101

22

20101201

23

20110101

21

20110201

20

 

Udvælgelse af bestemte dimensionsværdier til en beregnet analysevariabel

Udvælgelse via en attribut i en dimension. Dimension Leverandør, attribut Leverandør Navn:

CASE

  WHEN [Leverandør].[Leverandør Navn].CurrentMember IS [Leverandør].[Leverandør Navn].[EDB-butik] OR

           [Leverandør].[Leverandør Navn].CurrentMember IS [Leverandør].[Leverandør Navn].[IT-butik]

  THEN [Measures].[Beløb]

  ELSE NULL

END

Udvælgelse via et hierarki i en dimension. Dimension Leverandør, hierarki Leverandør, niveau Leverandør:

CASE

  WHEN [Leverandør].[Leverandør].CurrentMember IS [Leverandør].[Leverandør].[Leverandør].[EDB-butik] OR

           [Leverandør].[Leverandør].CurrentMember IS [Leverandør].[Leverandør].[Leverandør].[IT-butik]

  THEN [Measures].[Beløb]

  ELSE NULL

END

Fremfor at bruge teksten på dimensionsværdien kan man anvende key id nummeret f.eks.: &[123].

 

Andre calculated measure eksempler

[Mål for antal fraværsdage pr medarbejder] = -- hvor månedsniveauet for hele organisation er nogle

IIF                                                         -- andre måltal end for de enkelte under-organisationer,

(                                                           -- derfor kan de ikke summeres op men er i egen measuregroup.

([Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].&[82] OR

 [Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].&[86]) AND

[Measures].[Mål Måned] <> 0, '.', -- to organisationer har ingen summeret valide data, derfor vis .

IIF([Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].[All],

[Measures].[Mål Måned Hele Organisation], [Measures].[Mål Måned])

)

 

[Mål for antal fraværsdage pr medarbejder ÅTD] =

SUM(PERIODSTODATE([Tid].[År - Halvår - Kvartal - Måned].[År]), [Mål for antal fraværsdage pr medarbejder])

 

Alternativ løsning:

IIF

(

[Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].&[82] OR

[Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].&[86], '.',

CASE

  WHEN [Tid].[År - Halvår - Kvartal - Måned].CurrentMember.Level IS

           [Tid].[År - Halvår - Kvartal - Måned].[År] AND -- Årsniveau og total for hele organisationen.

           [Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].[All]

  THEN [Measures].[Mål År]

  WHEN [Tid].[År - Halvår - Kvartal - Måned].CurrentMember IS

          [Tid].[År - Halvår - Kvartal - Måned].[All] AND -- Total for alle år er en sum af år mål tallene.

          [Organisation].[Organisationsnavn].CurrentMember IS [Organisation].[Organisationsnavn].[All]

  THEN [Measures].[Mål År]

  ELSE [Measures].[Mål Måned]     -- Ved årsniveau/organisationer er det er en sum af måned mål tallene.

END               -- Ellers vises her på månedsniveau for de enkelte organisationer de enkelte måned mål tal.

)

 

Total niveauet skal altid være summen af progressionstyperne 1 + 2 + 3 uanset om der i Targit kriterier kun er valgt progressionstype 1. Her representerer 0 den ukendte progressionstype.

[Antal forløb] =

CASE

  WHEN [Progressionstype].[Progressionstype].CurrentMember IS [Progressionstype].[Progressionstype].&[0]

  THEN IIF([Measures].[Antal forløbx] = 0, NULL, 0)

  WHEN

   [Progressionstype].[Progressionstype].CurrentMember IS [Progressionstype].[Progressionstype].&[1] OR

   [Progressionstype].[Progressionstype].CurrentMember IS [Progressionstype].[Progressionstype].&[2] OR

   [Progressionstype].[Progressionstype].CurrentMember IS [Progressionstype].[Progressionstype].&[3]

  THEN

   ([Measures].[Antal forløbx], [Progressionstype].[Progressionstype].CurrentMember)

  ELSE – total niveauet

   ([Measures].[Antal forløbx], [Progressionstype].[Progressionstype].&[1]) +

   ([Measures].[Antal forløbx], [Progressionstype].[Progressionstype].&[2]) +

   ([Measures].[Antal forløbx], [Progressionstype].[Progressionstype].&[3])

END

 

Få fat i værdier:

[Kunde].[Kundenavn].CurrentMember.Member_Key

[Kunde].[Kundenavn].CurrentMember.Member_Value

[Kunde].[Kundenavn].CurrentMember.Member_Caption

[Kunde].[Kundenavn].CurrentMember.Uniquename

[Kunde].[Kundenavn].CurrentMember.Name

[Kunde].[Kundenavn].CurrentMember.Level.Ordinal

 

CREATE MEMBER CURRENTCUBE.[Measures].[Dette år]

 AS [År].CurrentMember.Member_Key

CREATE MEMBER CURRENTCUBE.[Measures].[Sidste år]

 AS [År].CurrentMember.Lag(1).Member_Key

CREATE MEMBER CURRENTCUBE.[Measures].[Næste år]

 AS [År].CurrentMember.Lead(1).Member_Key

CREATE MEMBER CURRENTCUBE.[Measures].[Posteringsdato dette år]

 AS [Posteringsdato].[Postering].CurrentMember.Member_Value

CREATE MEMBER CURRENTCUBE.[Measures].[Posteringsdato sidste år]

 AS UNION(

      PARALLELPERIOD([Posteringsdato].[Postering].[Posteringsår], 1,

                               [Posteringsdato].[Postering].CurrentMember),

      PARALLELPERIOD([Posteringsdato].[Postering].[Posteringsår], 1,

                               [Posteringsdato].[Postering].CurrentMember.Lag(1))

 ).item(0).Member_Value

 

fordi denne ikke virker for 29. februar:

PARALLELPERIOD([Posteringsdato].[Postering].[Posteringsår], 1,

                           [Posteringsdato].[Postering].CurrentMember).Member_Value

 

Beløb dette år, jeg anvender Key værdien derfor angivet & og yyyymmdd f.eks. 20160229:

(StrToMember("[Posteringsdato].[Posteringsdato].&[" + [År].[År].CurrentMember.Member_Key + Right([Posteringsdato].[Postering].CurrentMember.Member_Key,4) + "]"), [Measures].[Beløb])

 

Beløb sidste år, f.eks. 20160229 er sidste år 20150228:

(StrToMember("[Posteringsdato].[Posteringsdato].&[" + [År].[År].CurrentMember.Lag(1).Member_Key + IIF(Right([Posteringsdato].[Postering].CurrentMember.Member_Key,4) = "0229", "0228", Right([Posteringsdato].[Postering].CurrentMember.Member_Key,4)) + "]"), [År].[År].CurrentMember.Lag(1), [Measures].[Beløb])

 

Jeg ønsker at se omsætning frem til og med angivet Posteringsdato med følgende regel, at jeg tager dag og måned fra Posteringsdato og år fra År dimensionen og jeg sætter de tre værdier sammen til en dato årmåneddag som et heltal, med den undtagelse, at når året er større end dagsdato’s år, så anvendes det år i stedet for. Jeg har lavet to usynlige hjælpe calculated measures til at udregne dato for et år og for sid­ste år fordi der skal udregnes en indeks stigning eller fald i forhold til sidste år. Jeg bruger

Format(Now(),"yyyy") til at få fat i dagsdato og året der fra. Følgende beregnede analysevariable:

 

[Measures].[ÅrMånedDag]

IIF([År].[År].CurrentMember.Member_Key > Format(Now(),"yyyy"), Format(Now(),"yyyy"), [År].[År].CurrentMember.Member_Key) + IIF(Right([Posteringsdato].[Postering].CurrentMem­ber.Member_Key,4) = "0229", "0228", Right([Posteringsdato].[Postering].CurrentMember.Member_Key,4))

 

[Measures].[ÅrMånedDag sidsteår]

IIF([År].[År].CurrentMember.Lead(1).Member_Key > Format(Now(),"yyyy"), Format(Now(),"yyyy"), [År].[År].CurrentMember.Lag(1).Member_Key) + IIF(Right([Posteringsdato].[Postering].CurrentMem­ber.Member_Key,4) = "0229", "0228", Right([Posteringsdato].[Postering].CurrentMember.Member_Key,4))

 

[Measures].[Omsætning]

SUM({NULL:StrToMember("[Posteringsdato].[Posteringsdato].&[" + [Measures].[ÅrMånedDag] + "]")}, [Measures].[Beløb]),

 

[Measures].[Omsætning sidste år]

SUM({NULL:StrToMember("[Posteringsdato].[Posteringsdato].&[" + [Measures].[ÅrMånedDag sidsteår] + "]")}, ([År].[År].CurrentMember.Lag(1), [Measures].[Beløb]))

 

[Measures].[Omsætning indeks]

IIF([Measures].[Omsætning sidste år] = 0, NULL,

100*[Measures].[Omsætning]/[Measures].[Omsætning sidste år (book)])

 

Ved tre år i År dimensionen vist som rækker i en tabel giver Lag(1) forrige række til »sidste år«:

2015 Lag(1) giver data fra 2014.

2016 Lag(1) giver data fra 2015.

2017 Lag(1) giver data fra 2016.

 

I tilfælde at at År dimensionen er faldende sortering f.eks. 2017, 2016, 2015 så vil Lag(1) give »næste år«:

2017 Lag(1) giver data fra 2018.

2016 Lag(1) giver data fra 2017.

2015 Lag(1) giver data fra 2016.

Det er naturligvis forkert til indeks beregning i forhold til sidste år, derfor skal jeg ved faldende sortering i År dimensionen i stedet anvende Lead(1) som tager næste række:

2017 Lead(1) giver data fra 2016.

2016 Lead(1) giver data fra 2015.

2015 Lead(1) giver data fra 2014.

 

År dimension optræder som kolonner i en matrix med faldende sortering, omsætning og indeks tal:

 

Landsdel/År/Omsætning

2017

 

2016

 

2015

Vest Danmark

1.700

131

1.300

144

900

Øst Danmark

2.300

85

2.700

129

2.100

 

Attribute relationships diagram

 

 

Attribut indeholder NULL værdi – NullProcessing sættes til Preserve

 

 

Attribut gøres entydig via to felter fra dimensionstabellen

 

 

Analysevariabel (measure) karakteristik

·         Addictive, kan blive aggregeret (aggregate) med sum og antal (count) på alle dimensioner f.eks. om­sæt­ning og cprnr.

·         Semi-addictive, kun sum/antal på udvalgte dimensioner f.eks. realiseret beløb som er uafhængig af budget­­ver­sion dimensionen.

·         Non-addictive, kan aldrig summeres men i stedet vise max og min på f.eks. en pris.

 

Formatering af analysevariabel

Vigtigt at sætte formatering på analysevariabel (measure) og beregnet analysevariabel (calculated mea­sure) for at få tusinde adskillelse og bestemt antal decimaler, samt når en sum af negative og positive værdier i en ana­ly­se­va­ria­bel bliver nul, så at få vist 0 i analyseværktøjet f.eks. Excel eller Targit, så brugeren kan se, at der er en værdi i cellen og foretage drill through deri og se de bagvedliggende værdier (posteringsdata), mens en tom celle betyder ingen bagvedliggende værdier.

 

Heltalsformatering med tusinde adskillelse f.eks. et antal eller et beløb uden decimal:

FormatString på measure: #,#;-#,#;0               og på calculated measure: "#,#;-#,#;0"

                                      #,0;-#,0;0                                                       "#,0;-#,0;0"

 

Decimaltalsformatering med tusinde adskillelse med to decimaler f.eks. et beløb i kroner og øre:

FormatString på measure: #,0.00;-#,0.00;0.00   og på calculated measure: "#,0.00;-#,0.00;0.00"

 

Eller procent med en decimal og % tegnet: "#,0.0%;-#,0.0%;0.0%"

 

Formateringen angives med amerikansk notation d.v.s. komma for tusinde adskillelse og punktum som decimal­tal. I de enkelte kuber sættes Cube Structure egenskab Language til Danish, så formateringen vises rigtig i analyseværktøjet. Kan også sættes generelt for alle kuber i Analysis Services under Management Studio ved at højreklikke på servernavnet:

 

 

Feltændring i en dimensionstabel

Når man foretager en felt ændring i en dimensionstabel f.eks. attributnavn felt nvarchar fra 50 til 100 tegn, skal man foretage et touch af viewet mod olap kube databasen ved gå i design på viewet, foretage en ændring, gemme, foretage den omvendte ændring så man går tilbage til det oprindelige view og gemme igen. I olap kube databasen skal der også foretages en ændring, både i Data Source View via en Refresh og i dimensionen på sel­ve attributten:

 

 

Kopier en dimension mellem to kube solutions

Man kan ikke copy-paste dimensioner på tværs af kuber, men i stedet addere en eksisterende dimensionsfil (.dm) fra kilde solution til destination solution og desværre efterfølgende må rette i dimensionens xml kode.

1) I destination solution adderes en eksisterende dimension kaldet Tid fra en anden kilde solution:

2) I mappen for kilde solution findes den ønskede dimensionsfil, her Tid.dm:

3) Idet Tid dimensionen peger på kilde solution data view source ID, skal dette ændres, men først skal de­sti­na­tion solution data source view ID findes i koden, fordi ID ikke behøver at være det samme som dsv navnet, hvis det er blevet ændret efter oprettelsen af dsv:

4) ID for data source view er LIS hvilket huskes til senere:

5) Tid dimensionen åbnes i koden:

6) Der søges efter teksten DataSourceViewBinding:

7) DataSourceViewId viser navnet på kilde solutions data source view, hvilket skal ændres til LIS:

8) Koden gemmes og vinduet lukkes, og dimension kan åbnes på normalvis med Source pegende på LIS:

Keine Hexerei, nur Behändigkeit - ingen hekseri, kun behændighed, men det er sjældent, at Microsoft svigter en copy-paste eller smart importering!

 

Corrupted olap kube fil

Problem

Deploy a Analysis Services Project from SQL Server Business Intelligence Development Studio gives following error message: File system error: The following file is corrupted ... in one of the OLAP database.

Solution

1.    In SQL Server Management Studio by connection to Analysis Server, right click at the servername and select Stop the service.

2.    In Explorer rename the folder of the OLAP database that is located in folder:

     D:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data

     like put a letter x in front of the folder name of the OLAP database.

3.    In SQL Server Management Studio by connection to Analysis Server, right click at the servername and select Start the service.

4.    Right click again at the servername and select Refresh for updating the tree list.

5.    Left click at the + sign to open Database node in the tree.

6.    Right click at the OLAP database and select Delete. If you get an error message, then do Delete again and the OLAP database will be deleted in the ending.

7.    In SQL Server Business Intelligence Development Studio open the Analysis Services Project and getting the solution tree of the source code for the olap database with dataviews, dimensions and cubes.

8.    Right click at the Analysis Services Project name in solution tree and select Deploy, and the OLAP database will be created again in SQL Server Management Studio by connection to Analysis Server.

9.    In SQL Server Management Studio by connection to Analysis Server, right click at the servername and select Refresh and open Database folder and see the OLAP database that have the deployment again and ready for use by Excel, Targit, Report Services and other olap tools programs at the server, another server or users PC. Sometimes Deploy from SQL Server Business Intelligence Development Studio, when processing the dimensions and cubes gives many errors. When that happen, I always in SQL Server Mana­gement Studio by connection to Analysis Server, open Database and right click at the OLAP data­base and select Process and make a full process. That works always and give no errors. Of course if there is a data error in the datawarehouse database, then there will be many error messages, but nor­mal­ly the last line in process window will tell, which data value in which dimension or cube is making the error, so the data can be fix in the datawarehouse database and a new process can be done from in SQL Server Mana­gement Studio with a successfull result.

10. See a new folder for the OLAP database in in folder:

     D:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data.

11. Delete the renamed folder in folder:

     D:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data.

 

Perspectives (findes kun i Enterprise edition sammen med Translations)

En kube, her kaldet en superkube, består ofte af flere measuregroups, som både har fælles og egne dimensioner. Når ku­ben tilgås via Excel, Targit, Business Objects m.fl. præsenteres man for alle dimensioner og analysevaria­ble, men det er ikke alle som kan kombineres frit i en analyse, fordi nogle measuregroups har egne dimensio­ner. Med anvendelse af perspektiver kan man samle dimensioner og analysevariable som hører sammen in­klu­siv de beregnede analysevariable i flere forskellige navngivne subkuber, hvorved en subkube er: »a slice of the cube, and is far from same same as a virtual cube in SQL Server 7 and 2000.« En superkube med to eller flere measuregroups er at sammenligne med den tidligere virtuelle kube, hvor en measuregroup er lig med en kube i SQL Server 7 og 2000, og superkuben in­de­hol­der: »the virtual union between measure­groups with even different grain.« Når en kubedatabase indeholder flere superkuber med fælles dimensioner med flere hierarkier, giver perspektiv mulighed for at angive hvilke hierar­ki­er der skal vises i hvilke superkuber og subkuber. Det er således enkelt via flueben at vise eller skjule kube elementer inkl. drill through (actions).

En bruger i eksempelvis Excel eller Targit vil først starte med at væl­ge en subkube, hvorefter brugeren præ­sen­te­res for de dimensioner og analysevariable som frit kan kombineres i en analyse.

Med Trans­la­tions kan kube elementernes navngives mere præcist på dansk og oversættes til andre sprog.

 

I AS 2005 og 2008 er der en fejl i Perspectives, når man vælger ikke at markere nogen analysevariabel i et per­spek­tiv, fordi der kun skal vises beregnet analysevariable (calculated measures) som er markeret i per­spek­ti­vet. Fejlen består i, at analysevariabel fra et andet perspektiv bliver vist i det perspektiv, som ingen analysevariabel har markeret. Det er vigtigt, altid at have mindst en analysevariabel markeret i et perspektiv, og det kan sagtens være en analysevariabel hvis egenskab Visible er False. Nedenfor vises markering af en analysevariabel i perspektivet »Rettidighed - Aktivering« selvom dens Visible er False, men derved ungås det, at de rigtig markerede analysevariable fra perspektivet »Rettidighed - Jobsamtaler« bliver vist i perspektivet »Rettidighed - Aktivering«. Naturligvis har perspektivet »Rettidighed - Aktivering« markering af en række be­reg­net analysevariable nederst i perspektivet:

 

Ikoner med et navn

 

Fact dimension

Reference dimension

Mange-til-man­ge

dimension

Data mining dimension

Measuregroup

knyttet til en fact tabel

Dimension i en kube

Dimension fælles for kuber+measuregroups

Attribut i en dimension

Niveau hierarki i en

dimension

MorBarn hierarki i en dimension

Analysevariabel

measure

Beregnet analysevar.

calcu­lated measure

Kube med flere

measuregroups

Perspektiv udvælger di­mensioner og analyser.

KPI visning

Key Performance

Action der udføres fra analyse-rapværktøj

Named set er en del­mængde af dimværdier

Primærnøgle (logisk)

 

Kommentar (comment) i SSIS pakke

Findes ikke som objekt i Toolbox. I stedet højreklikkes et sted i designer surface og vælges Add Annotation. Så fås en firkant hvori teksten kan skrives. Ved højreklik på firkanten kan font sættes. Linieskift i firkanten ved Ctrl+Enter.