Förstå star-schema och vikten för Power BI

Den här artikeln riktar sig till Power BI Desktop-datamodellerare. Den beskriver star-schemadesign och dess relevans för att utveckla Power BI-datamodeller som är optimerade för prestanda och användbarhet.

Den här artikeln är inte avsedd att ge en fullständig diskussion om star-schemadesign. Mer information finns direkt i publicerat innehåll, till exempel The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd edition, 2013) av Ralph Kimball med flera.

Översikt över Star-schema

Star-schema är en mogen modelleringsmetod som används i stor utsträckning av relationsdatalager. Det kräver att modellerare klassificerar sina modelltabeller som antingen dimension eller fakta.

Dimensionstabeller beskriver affärsentiteter – de saker som du modellerar. Entiteter kan innehålla produkter, personer, platser och begrepp, inklusive själva tiden. Den mest konsekventa tabellen som du hittar i ett stjärnschema är en datumdimensionstabell. En dimensionstabell innehåller en nyckelkolumn (eller kolumner) som fungerar som en unik identifierare och beskrivande kolumner.

Faktatabeller lagrar observationer eller händelser och kan vara försäljningsorder, lagersaldon, växelkurser, temperaturer osv. En faktatabell innehåller dimensionsnyckelkolumner som relaterar till dimensionstabeller och numeriska måttkolumner. Dimensionsnyckelkolumnerna bestämmer dimensionaliteten i en faktatabell, medan dimensionsnyckelvärdena bestämmer kornigheten i en faktatabell. Tänk dig till exempel en faktatabell som är utformad för att lagra försäljningsmål som har två dimensionsnyckelkolumner Datum och ProductKey. Det är lätt att förstå att tabellen har två dimensioner. Kornigheten kan dock inte fastställas utan hänsyn till dimensionsnyckelvärdena. I det här exemplet bör du tänka på att de värden som lagras i kolumnen Datum är den första dagen i varje månad. I det här fallet är kornigheten på månadsnivå.

I allmänhet innehåller dimensionstabeller ett relativt litet antal rader. Faktatabeller kan å andra sidan innehålla ett mycket stort antal rader och fortsätta växa med tiden.

Normalisering jämfört med avnormalisering

För att förstå några star-schemabegrepp som beskrivs i den här artikeln är det viktigt att känna till två termer: normalisering och avnormalisering.

Normalisering är den term som används för att beskriva data som lagras på ett sätt som minskar repetitious-data. Överväg en tabell med produkter som har en unik nyckelvärdekolumn, till exempel produktnyckeln, och ytterligare kolumner som beskriver produktegenskaper, inklusive produktnamn, kategori, färg och storlek. En försäljningstabell anses normaliserad när den endast lagrar nycklar, till exempel produktnyckeln. Observera att endast kolumnen ProductKey registrerar produkten i följande bild.

Men om försäljningstabellen lagrar produktinformation utanför nyckeln anses den avnormaliserad. Observera att produktnyckeln och andra produktrelaterade kolumner registrerar produkten i följande bild.

När du hämtar data från en exportfil eller ett dataextrahering är det troligt att det representerar en avnormaliserad uppsättning data. I det här fallet använder du Power Query för att transformera och forma källdata till flera normaliserade tabeller.

Som beskrivs i den här artikeln bör du sträva efter att utveckla optimerade Power BI-datamodeller med tabeller som representerar normaliserade fakta- och dimensionsdata. Det finns dock ett undantag där en snowflake-dimension bör avnormaliseras för att skapa en enda modelltabell.

Star-schema relevans för Power BI-modeller

Star-schemadesign och många relaterade begrepp som introduceras i den här artikeln är mycket relevanta för att utveckla Power BI-modeller som är optimerade för prestanda och användbarhet.

Tänk på att varje visuellt Power BI-rapportobjekt genererar en fråga som skickas till Power BI-modellen (som Power BI-tjänst anropar en semantisk modell – som tidigare kallades en datamängd). Dessa frågor används för att filtrera, gruppera och sammanfatta modelldata. En väl utformad modell är alltså en modell som tillhandahåller tabeller för filtrering och gruppering samt tabeller för sammanfattning. Den här designen passar bra med star-schemaprinciper:

• Dimensionstabeller stöder filtrering och gruppering
• Faktatabeller stöder sammanfattning

Det finns ingen tabellegenskap som modellerare ställer in för att konfigurera tabelltypen som dimension eller fakta. Det bestäms faktiskt av modellrelationerna. En modellrelation upprättar en filterspridningssökväg mellan två tabeller och det är egenskapen Cardinality för relationen som avgör tabelltypen. En vanlig relations kardinalitet är en-till-många eller dess omvända många-till-en. Sidan "en" är alltid en tabell av dimensionstyp, medan "många"-sidan alltid är en tabell av faktatyp. Mer information om relationer finns i Modellrelationer i Power BI Desktop.

En välstrukturerad modelldesign bör innehålla tabeller som antingen är tabeller av dimensionstyp eller tabeller av faktatyp. Undvik att blanda ihop de två typerna för en enda tabell. Vi rekommenderar också att du strävar efter att leverera rätt antal tabeller med rätt relationer på plats. Det är också viktigt att tabeller av faktatyp alltid läser in data i ett konsekvent korn.

Slutligen är det viktigt att förstå att optimal modelldesign är en del av vetenskap och delkonst. Ibland kan du bryta med bra vägledning när det är vettigt att göra det.

Det finns många ytterligare begrepp som rör star-schemadesign som kan tillämpas på en Power BI-modell. Dessa begrepp omfattar:

• Åtgärder
• Surrogatnycklar
• Snowflake-dimensioner
• Rollspelsdimensioner
• Långsamt föränderliga dimensioner
• Skräpdimensioner
• Degenererade dimensioner
• Faktalösa faktatabeller

Mått

I star-schemadesign är ett mått en faktatabellkolumn som lagrar värden som ska sammanfattas.

I en Power BI-modell har ett mått en annan, men liknande, definition. Det är en formel skriven i DAX (Data Analysis Expressions) som uppnår sammanfattning. Måttuttryck använder ofta DAX-aggregeringsfunktioner som SUM, MIN, MAX, AVERAGE osv. för att skapa ett skalärt värderesultat vid frågetillfället (värden lagras aldrig i modellen). Måttuttrycket kan variera från enkla kolumnaggregeringar till mer avancerade formler som åsidosätter filterkontext och/eller relationsspridning. Mer information finns i artikeln DAX Basics i Power BI Desktop .

Det är viktigt att förstå att Power BI-modeller stöder en andra metod för att uppnå sammanfattning. Valfri kolumn – och vanligtvis numeriska kolumner – kan sammanfattas av ett visuellt rapportobjekt eller Q&A. Dessa kolumner kallas implicita mått. De erbjuder en bekvämlighet för dig som modellutvecklare, eftersom du i många fall inte behöver skapa mått. Till exempel kan kolumnen Sales Amount för Adventure Works-återförsäljare sammanfattas på flera sätt (summa, antal, medelvärde, median, min, max osv.), utan att du behöver skapa ett mått för varje möjlig sammansättningstyp.

Det finns dock tre övertygande skäl för dig att skapa mått, även för enkla sammanfattningar på kolumnnivå:

• När du vet att rapportförfattarna kommer att köra frågor mot modellen med hjälp av flerdimensionella uttryck (MDX) måste modellen innehålla explicita mått. Explicita mått definieras med hjälp av DAX. Den här designmetoden är mycket relevant när en Power BI-datauppsättning efterfrågas med hjälp av MDX, eftersom MDX inte kan uppnå sammanfattning av kolumnvärden. I synnerhet används MDX när du utför Analysera i Excel, eftersom pivottabeller utfärdar MDX-frågor.
• När du vet att rapportförfattarna skapar sidnumrerade Power BI-rapporter med hjälp av MDX-frågedesignern måste modellen innehålla explicita mått. Endast MDX-frågedesignern stöder serveraggregat. Om rapportförfattarna behöver ha mått utvärderade av Power BI (i stället för av den sidnumrerade rapportmotorn) måste de använda MDX-frågedesignern.
• När du behöver se till att rapportförfattarna bara kan sammanfatta kolumner på specifika sätt. Till exempel kan kolumnen Enhetspris för återförsäljares försäljning (som representerar ett pris per enhet) sammanfattas, men bara med hjälp av specifika aggregeringsfunktioner. Det bör aldrig summeras, men det är lämpligt att sammanfatta med hjälp av andra aggregeringsfunktioner som min, max, genomsnitt osv. I det här fallet kan modelleraren dölja kolumnen Enhetspris och skapa mått för alla lämpliga aggregeringsfunktioner.

Den här designmetoden fungerar bra för rapporter som skapats i Power BI-tjänst och för Q&A. Power BI Desktop-liveanslutningar gör det dock möjligt för rapportförfattare att visa dolda fält i fönstret Fält , vilket kan leda till att den här designmetoden kringgås.

Surrogatnycklar

En surrogatnyckel är en unik identifierare som du lägger till i en tabell för att stödja star-schemamodellering. Per definition är den inte definierad eller lagrad i källdata. Surrogatnycklar läggs vanligtvis till i relationsdatalagerdimensionstabeller för att tillhandahålla en unik identifierare för varje dimensionstabellrad.

Power BI-modellrelationer baseras på en enda unik kolumn i en tabell, som sprider filter till en enda kolumn i en annan tabell. När en tabell av dimensionstyp i din modell inte innehåller en enda unik kolumn måste du lägga till en unik identifierare för att bli "en" sida av en relation. I Power BI Desktop kan du enkelt uppnå det här kravet genom att skapa en Power Query-indexkolumn.

Du måste sammanfoga den här frågan med frågan "många"-sidan så att du kan lägga till indexkolumnen i den också. När du läser in dessa frågor till modellen kan du sedan skapa en en-till-många-relation mellan modelltabellerna.

Snowflake-dimensioner

En snowflake-dimension är en uppsättning normaliserade tabeller för en enskild affärsentitet. Adventure Works klassificerar till exempel produkter efter kategori och underkategori. Produkter tilldelas till underkategorier och underkategorier tilldelas i sin tur till kategorier. I relationsinformationslagret för Adventure Works normaliseras och lagras produktdimensionen i tre relaterade tabeller: DimProductCategory, DimProductSubcategory och DimProduct.

Om du använder din fantasi kan du föreställa dig de normaliserade tabellerna som placeras utåt från faktatabellen och bilda en snowflake-design.

I Power BI Desktop kan du välja att efterlikna en snowflake-dimensionsdesign (kanske för att dina källdata gör det) eller integrera (avnormalisera) källtabellerna i en enda modelltabell. I allmänhet uppväger fördelarna med en enskild modelltabell fördelarna med flera modelltabeller. Det mest optimala beslutet kan bero på datavolymerna och användbarhetskraven för modellen.

När du väljer att efterlikna en snowflake-dimensionsdesign:

• Power BI läser in fler tabeller, vilket är mindre effektivt ur lagrings- och prestandaperspektiv. Dessa tabeller måste innehålla kolumner för att stödja modellrelationer, och det kan resultera i en större modellstorlek.
• Längre distributionskedjor för relationsfilter måste bläddras igenom, vilket sannolikt är mindre effektivt än filter som tillämpas på en enda tabell.
• Fönstret Fält visar fler modelltabeller för rapportförfattare, vilket kan resultera i en mindre intuitiv upplevelse, särskilt när snowflake-dimensionstabeller bara innehåller en eller två kolumner.
• Det går inte att skapa en hierarki som sträcker sig över tabellerna.

När du väljer att integrera i en enskild modelltabell kan du också definiera en hierarki som omfattar dimensionens högsta och lägsta korn. Eventuellt kan lagring av redundanta avnormaliserade data leda till ökad modelllagringsstorlek, särskilt för mycket stora dimensionstabeller.

Långsamt föränderliga dimensioner

En långsamt föränderlig dimension (SCD) är en dimension som hanterar ändringar av dimensionsmedlemmar över tid. Det gäller när affärsentitetsvärden ändras över tid och på ett ad hoc-sätt. Ett bra exempel på en långsamt föränderlig dimension är en kunddimension, särskilt dess kolumner för kontaktinformation som e-postadress och telefonnummer. Däremot anses vissa dimensioner förändras snabbt när ett dimensionsattribut ändras ofta, till exempel en akties marknadspris. Den vanliga designmetoden i dessa instanser är att lagra snabbt föränderliga attributvärden i ett faktatabellmått.

Designteori för star-schema refererar till två vanliga SCD-typer: Typ 1 och Typ 2. En tabell av dimensionstyp kan vara typ 1 eller typ 2, eller stödja båda typerna samtidigt för olika kolumner.

Typ 1 SCD

En SCD av typ 1återspeglar alltid de senaste värdena, och när ändringar i källdata identifieras skrivs dimensionstabelldata över. Den här designmetoden är vanlig för kolumner som lagrar tilläggsvärden, till exempel en kunds e-postadress eller telefonnummer. När en kunds e-postadress eller telefonnummer ändras uppdaterar dimensionstabellen kundraden med de nya värdena. Det är som om kunden alltid hade den här kontaktinformationen.

En icke-inkrementell uppdatering av en power BI-modell med dimensionstyp uppnår resultatet av en SCD av typ 1. Den uppdaterar tabelldata för att säkerställa att de senaste värdena läses in.

Typ 2 SCD

En SCD av typ 2stöder versionshantering av dimensionsmedlemmar. Om källsystemet inte lagrar versioner är det vanligtvis informationslagrets inläsningsprocess som identifierar ändringar och hanterar ändringen i en dimensionstabell på rätt sätt. I det här fallet måste dimensionstabellen använda en surrogatnyckel för att ge en unik referens till en version av dimensionsmedlemmen. Den innehåller också kolumner som definierar datumintervallets giltighet för versionen (till exempel StartDate och EndDate) och eventuellt en flaggkolumn (till exempel IsCurrent) för att enkelt filtrera efter aktuella dimensionsmedlemmar.

Adventure Works tilldelar till exempel säljare till en försäljningsregion. När en säljare flyttar region måste en ny version av säljaren skapas för att säkerställa att historiska fakta förblir associerade med den tidigare regionen. För att stödja korrekt historisk analys av försäljning efter säljare måste dimensionstabellen lagra versioner av säljare och deras associerade regioner. Tabellen bör också innehålla start- och slutdatumvärden för att definiera tids giltigheten. Aktuella versioner kan definiera ett tomt slutdatum (eller 12/31/9999), vilket anger att raden är den aktuella versionen. Tabellen måste också definiera en surrogatnyckel eftersom affärsnyckeln (i det här fallet medarbetar-ID) inte är unik.

Det är viktigt att förstå att när källdata inte lagrar versioner måste du använda ett mellanliggande system (t.ex. ett informationslager) för att identifiera och lagra ändringar. Tabellinläsningsprocessen måste bevara befintliga data och identifiera ändringar. När en ändring identifieras måste tabellinläsningsprocessen förfalla den aktuella versionen. Den registrerar dessa ändringar genom att uppdatera EndDate-värdet och infoga en ny version med StartDate-värdet från föregående EndDate-värde . Dessutom måste relaterade fakta använda en tidsbaserad sökning för att hämta dimensionsnyckelvärdet som är relevant för faktadatumet. En Power BI-modell som använder Power Query kan inte generera det här resultatet. Den kan dock läsa in data från en förinläst SCD-dimensionstabell av typ 2.

Power BI-modellen bör ha stöd för att fråga efter historiska data för en medlem, oavsett ändring, och för en version av medlemmen, som representerar ett visst tillstånd för medlemmen i tid. I samband med Adventure Works gör den här designen att du kan fråga säljaren oavsett tilldelad försäljningsregion eller för en viss version av säljaren.

För att uppnå detta krav måste power BI-modellens dimensionstypstabell innehålla en kolumn för filtrering av säljaren och en annan kolumn för filtrering av en specifik version av säljaren. Det är viktigt att versionskolumnen innehåller en icke-tvetydig beskrivning, till exempel "Michael Blythe (12/15/2008-06/26/2019)" eller "Michael Blythe (aktuell)". Det är också viktigt att utbilda rapportförfattare och konsumenter om grunderna i SCD Typ 2 och hur du uppnår lämpliga rapportdesigner genom att använda rätt filter.

Det är också en bra designmetod att inkludera en hierarki som gör att visuella objekt kan öka detaljnivån till versionsnivån.

Dimensioner med olika roller

En rollspelsdimension är en dimension som kan filtrera relaterade fakta på olika sätt. I Adventure Works har till exempel datumdimensionstabellen tre relationer till återförsäljarnas försäljningsfakta. Samma dimensionstabell kan användas för att filtrera fakta efter orderdatum, leveransdatum eller leveransdatum.

I ett informationslager är den godkända designmetoden att definiera en enda datumdimensionstabell. Vid frågetillfället upprättas "rollen" för datumdimensionen med vilken faktakolumn du använder för att ansluta tabellerna. När du till exempel analyserar försäljning efter orderdatum relaterar tabellkopplingen till kolumnen försäljningsorderdatum för återförsäljare.

I en Power BI-modell kan den här designen imiteras genom att skapa flera relationer mellan två tabeller. I Adventure Works-exemplet skulle tabellerna för datum- och återförsäljares försäljning ha tre relationer. Även om den här designen är möjlig är det viktigt att förstå att det bara kan finnas en aktiv relation mellan två Power BI-modelltabeller. Alla återstående relationer måste vara inaktiva. Att ha en enda aktiv relation innebär att det finns en standardfilterspridning från datum till återförsäljares försäljning. I det här fallet är den aktiva relationen inställd på det vanligaste filtret som används av rapporter, som på Adventure Works är orderdatumrelationen.

Det enda sättet att använda en inaktiv relation är att definiera ett DAX-uttryck som använder funktionen USERELATIONSHIP. I vårt exempel måste modellutvecklaren skapa mått för att möjliggöra analys av återförsäljares försäljning efter leveransdatum och leveransdatum. Det här arbetet kan vara omständligt, särskilt när återförsäljartabellen definierar många mått. Den skapar också e-post i fältfönstret med ett överflöd av mått. Det finns även andra begränsningar:

• När rapportförfattarna förlitar sig på att sammanfatta kolumner, i stället för att definiera mått, kan de inte uppnå sammanfattning för de inaktiva relationerna utan att skriva ett mått på rapportnivå. Mått på rapportnivå kan bara definieras vid redigering av rapporter i Power BI Desktop.
• Med bara en aktiv relationssökväg mellan datum- och återförsäljares försäljning går det inte att samtidigt filtrera återförsäljares försäljning efter olika typer av datum. Du kan till exempel inte skapa ett visuellt objekt som ritar orderdatumförsäljning genom levererad försäljning.

För att övervinna dessa begränsningar är en vanlig Power BI-modelleringsteknik att skapa en tabell av dimensionstyp för varje rollspelsinstans. Du skapar vanligtvis de ytterligare dimensionstabellerna som beräknade tabeller med DAX. Med hjälp av beräknade tabeller kan modellen innehålla en datumtabell , en leveransdatumtabell och en leveransdatumtabell , var och en med en enda och aktiv relation till sina respektive kolumner i försäljningstabellen för återförsäljare.

Den här designmetoden kräver inte att du definierar flera mått för olika datumroller och tillåter samtidig filtrering efter olika datumroller. Ett lägre pris att betala med den här designmetoden är dock att det kommer att finnas duplicering av datumdimensionstabellen som resulterar i en ökad modelllagringsstorlek. Eftersom tabeller av dimensionstyp vanligtvis lagrar färre rader i förhållande till tabeller av faktatyp är det sällan ett problem.

Observera följande metodtips när du skapar tabeller av modelldimensionstyp för varje roll:

• Kontrollera att kolumnnamnen är självbeskrivande. Även om det är möjligt att ha en Year-kolumn i alla datumtabeller (kolumnnamnen är unika i tabellen) är det inte självbeskrivande som standardrubriker för visuella objekt. Överväg att byta namn på kolumner i varje dimensionsrolltabell, så att tabellen Leveransdatum har en årskolumn med namnet Ship Year osv.
• När det är relevant ska du se till att tabellbeskrivningar ger feedback till rapportförfattare (via knappbeskrivningar för fältfönstret ) om hur filterspridning konfigureras. Den här tydligheten är viktig när modellen innehåller en allmänt namngiven tabell, till exempel Datum, som används för att filtrera många tabeller av faktatyp. Om den här tabellen till exempel har en aktiv relation till kolumnen försäljningsorderdatum för återförsäljare kan du överväga att ange en tabellbeskrivning som "Filtrerar återförsäljares försäljning efter orderdatum".

Mer information finns i Vägledning för aktiva kontra inaktiva relationer.

Skräpdimensioner

En skräpdimension är användbar när det finns många dimensioner, särskilt som består av få attribut (kanske en), och när dessa attribut har få värden. Bra kandidater inkluderar orderstatuskolumner eller kunddemografikolumner (kön, åldersgrupp osv.).

Designmålet för en skräpdimension är att konsolidera många "små" dimensioner till en enda dimension för att både minska modellens lagringsstorlek och även minska oredan i fältfönstret genom att visa färre modelltabeller.

En skräpdimensionstabell är vanligtvis kartesisk produkt för alla dimensionsattributmedlemmar, med en surrogatnyckelkolumn. Surrogatnyckeln ger en unik referens till varje rad i tabellen. Du kan skapa dimensionen i ett informationslager eller genom att använda Power Query för att skapa en fråga som utför fullständiga yttre frågekopplingar och sedan lägga till en surrogatnyckel (indexkolumn).

Du läser in den här frågan till modellen som en tabell av dimensionstyp. Du måste också sammanfoga den här frågan med faktafrågan, så att indexkolumnen läses in i modellen för att stödja skapandet av en "en-till-många"-modellrelation.

Degenerera dimensioner

En degenererad dimension refererar till ett attribut för faktatabellen som krävs för filtrering. På Adventure Works är återförsäljarens försäljningsordernummer ett bra exempel. I det här fallet är det inte bra modelldesign att skapa en oberoende tabell som bara består av den här kolumnen, eftersom det skulle öka modellens lagringsstorlek och resultera i att fönstret Fält blir rörigt.

I Power BI-modellen kan det vara lämpligt att lägga till kolumnen försäljningsordernummer i tabellen av faktatyp för att tillåta filtrering eller gruppering efter försäljningsordernummer. Det är ett undantag från den tidigare införda regeln att du inte ska blanda tabelltyper (i allmänhet bör modelltabeller vara av dimensionstyp eller faktatyp).

Men om adventure works-återförsäljarnas försäljningstabell har kolumner för ordernummer och orderradsnummer, och de krävs för filtrering, skulle en degenererad dimensionstabell vara en bra design. Mer information finns i Vägledning om en-till-en-relation (Degenererade dimensioner).

Faktalösa faktatabeller

En faktalös faktatabell innehåller inga måttkolumner. Den innehåller endast dimensionsnycklar.

En faktalös faktatabell kan lagra observationer som definierats av dimensionsnycklar. Vid ett visst datum och en viss tidpunkt loggade till exempel en viss kund in på din webbplats. Du kan definiera ett mått för att räkna raderna i den faktalösa faktatabellen för att analysera när och hur många kunder som har loggat in.

En mer övertygande användning av en faktalös faktatabell är att lagra relationer mellan dimensioner, och det är designmetoden för Power BI-modellen som vi rekommenderar att du definierar många-till-många-dimensionsrelationer. I en många-till-många-dimensionsrelationsdesign kallas den faktalösa faktatabellen för en bryggningstabell.

Anta till exempel att säljare kan tilldelas till en eller flera försäljningsregioner. Bryggningstabellen skulle utformas som en faktalös faktatabell som består av två kolumner: säljnyckel och regionnyckel. Dubblettvärden kan lagras i båda kolumnerna.

Den här designmetoden för många-till-många är väldokumenterad och kan uppnås utan en bryggningstabell. Bryggningstabellmetoden anses dock vara den bästa metoden när två dimensioner relateras. Mer information finns i Vägledning för många-till-många-relationer (Relatera två tabeller av dimensionstyp).

Relaterat innehåll

Mer information om design av star-schema eller Power BI-modelldesign finns i följande artiklar:

• Wikipedia-artikel om dimensionsmodellering
• Skapa och hantera relationer i Power BI Desktop
• Vägledning för en-till-en-relation
• Vägledning för många-till-många-relationer
• Vägledning för dubbelriktad relation
• Vägledning för aktiva kontra inaktiva relationer
• Frågor? Prova att fråga Power BI Community
• Förslag? Bidra med idéer för att förbättra Power BI