Power BI-kobling til SQL Server og Access – Komplett guide

Dataene dine ligger i en SQL Server-database eller en Access-fil. Power BI kan visualisere dem. Men hvordan kobler du dem sammen på riktig måte? Og hva er forskjellen mellom DirectQuery og Import? Denne guiden viser nøyaktig hvordan.

Hos Excel Department har vi koblet Power BI til over 100 databaser – fra små Access-filer til SQL Server i store virksomheter med milliarder av rader. Vi har gjort alle feilene slik at du slipper. Denne guiden gir deg metodene som faktisk fungerer.

Hvorfor koble Power BI til database i stedet for Excel?

Før vi går inn på det tekniske, la oss snakke om hvorfor.

Begrensninger i Excel: – Maks omtrent 1 million rader (tregt lenge før det) – Manuell oppdatering eller kompleks VBA – Risiko for at data blir utdaterte – Vanskelig å håndtere mange brukere

Fordeler med database: – Millioner eller milliarder av rader uten problemer – Alltid oppdaterte data (Power BI leser direkte fra kilden) – Strukturerte og relasjonelle data – Én sannhetskilde for hele organisasjonen

Når du bør koble til database: – Data oppdateres kontinuerlig (salg, transaksjoner, logging) – Datamengden vokser ut over Excels kapasitet – Flere systemer bruker de samme dataene – Du vil garantere datakonsistens

Hvis du allerede har SQL-koblet Excel i dag, er steget til Power BI lite, men gir mye mer kraft.

SQL Server: DirectQuery vs Import mode

Dette er det første store beslutningspunktet. Begge måtene fungerer, men de passer for ulike situasjoner.

Import mode (anbefalt for de fleste)

Slik fungerer det: Power BI leser data fra SQL Server, komprimerer dem og lagrer dem lokalt i Power BI-modellen. Når rapporten åpnes, leser den fra denne kopien, ikke fra SQL Server.

Fordeler: – Mye raskere rapporter (dataene er lokale) – Fungerer offline (dataene ligger i filen) – Mindre belastning på SQL Server – Power BIs komprimering reduserer datastørrelsen (ofte 10x)

Ulemper: – Dataene er ikke sanntid (oppdateres etter en tidsplan) – Begrensning på datavolum (modellstørrelse maks 10 GB for Pro, 100+ GB for Premium) – Krever en oppdateringsplan

Passer for: – Datavarehus eller rapporteringsdatabase – Data som oppdateres daglig eller ukentlig – Historisk analyse – Når rask ytelse er kritisk – De fleste business intelligence-scenarier

Eksempel: En salgsdatabase med 5 millioner rader, oppdatert hver natt kl. 02:00. Power BI henter nye data da. Brukerne får lynraske rapporter i løpet av dagen.

DirectQuery mode

Slik fungerer det: Power BI sender SQL-spørringer direkte til SQL Server hver gang du samhandler med rapporten. Ingen data lagres lokalt.

Fordeler: – Ekte sanntidsdata (alltid oppdatert) – Ingen begrensning på datastørrelse – Sikkerhet håndteres av SQL Server

Ulemper: – Tregere rapporter (hvert klikk = en SQL-spørring) – Krever konstant tilkobling til SQL Server – Høyere belastning på databasen – Begrensede DAX-funksjoner (noen mål fungerer ikke)

Passer for: – Sanntidsdata der hvert minutt teller (kasser, trading, IoT) – Ekstremt store datasett (100+ GB) – Streng datasikkerhet (data får ikke forlate serveren) – Operasjonell rapportering

Eksempel: Et logistikkdashboard som viser gjeldende lagernivåer. Hver gang noen plukker et produkt oppdateres databasen, og Power BI viser det nye tallet i løpet av sekunder.

Hybrid: Composite models

Siden 2019 kan du kombinere begge. Noen tabeller i Import, andre i DirectQuery.

Typisk bruksområde: – Store transaksjonsdata i DirectQuery (sanntid) – Mindre masterdata (produkter, kunder) i Import (ytelse)

Komplisert, men kraftfullt. Vi anbefaler konsulenthjelp til dette.

Vår anbefaling

Start med Import mode i 95 % av tilfellene.

DirectQuery er for spesialtilfeller. De fleste virksomheter trenger ikke sanntidsdata – daglig oppdatering er nok. Import gir bedre ytelse og er enklere å håndtere.

Bytt til DirectQuery bare hvis: 1. Du virkelig trenger sanntidsdata (bevis det) 2. Dataene dine er >50 GB og vokser raskt 3. Datasikkerheten krever at data ikke kopieres

Steg for steg: Koble til SQL Server

Nå blir det praktisk. Slik gjør du det:

Steg 1: Forbered SQL Server

Du trenger: – Servernavn (eksempel: sqlserver.dinserver.se) – Databasenavn – Påloggingsopplysninger (Windows Authentication eller SQL Server Authentication) – Nettverkstilgang til serveren

Test tilgangen først: Åpne SQL Server Management Studio (SSMS) og koble til med de samme opplysningene. Hvis det ikke fungerer der, fungerer det heller ikke i Power BI.

Brannmurregler: Hvis SQL Server er on-premises bak en bedriftsbrannmur, må du åpne port 1433 (standard SQL Server-port).

For Azure SQL: Legg til IP-adressen din i Azure Portal under Firewall settings.

Steg 2: Koble til fra Power BI Desktop

Åpne Power BI Desktop.

Home → Get Data → SQL Server

Fyll ut: – Server: sqlserver.dinserver.se eller dinserver.database.windows.net (Azure) – Database: Navnet på databasen – Data Connectivity mode: Import (anbefalt)

Advanced options (valgfritt): – SQL statement: Hvis du vil skrive din egen SQL-spørring – Command timeout: Øk hvis store spørringer tar lang tid

Klikk OK.

Steg 3: Autentisering

Velg autentiseringsmetode:

Windows Authentication: Bruker Windows-påloggingsopplysningene dine. Fungerer for on-premises SQL Server hvis du er pålogget bedriftsnettverket.

Database: SQL Server-brukernavn og passord. Standard for Azure SQL.

Microsoft Account: For Azure SQL kan du bruke Microsoft 365-kontoen din.

Klikk Connect.

Steg 4: Velg tabeller

Navigator-vinduet viser alle tabeller og visninger i databasen.

Tips: – Velg bare tabellene du trenger (ikke hele databasen) – Se på datastørrelsen (vises i Navigator) – Foretrekk visninger framfor basistabeller hvis IT har laget dem for deg

Marker tabeller → klikk Transform Data (ikke Load direkte).

Steg 5: Transformer i Power Query

Nå ser du tabellene dine i Power Query Editor.

Vanlige transformasjoner: – Fjern kolonner du ikke trenger (reduserer datastørrelsen) – Filtrer rader (for eksempel bare de siste 2 årene) – Endre datatyper hvis Power BI har gjettet feil – Merge queries for å opprette relasjoner

Viktig: All filtrering i Power Query reduserer datamengden = raskere rapporter.

Klikk Close & Apply når du er ferdig.

Steg 6: Sett opp automatisk oppdatering (etter publisering)

Publiser rapporten til Power BI Service.

Gå til innstillingene for datasettet: – Scheduled refresh: Velg frekvens og tidspunkt – Gateway: Hvis SQL Server er on-premises (se nedenfor) – Credentials: Oppgi databaseopplysningene

Merk: For on-premises SQL Server kreves en Power BI Gateway. Mer om det nedenfor.

Koble til Microsoft Access

Access-databaser er vanlige i små svenske virksomheter. Power BI kan koble til dem, men med begrensninger.

Steg 1: Koble til en Access-fil

Home → Get Data → Access Database

Bla til .accdb- eller .mdb-filen din → Open.

Navigator viser tabeller og spørringer (lagrede spørringer i Access).

Velg det du trenger → Transform Data.

Steg 2: Transformer data

Access-databaser er ofte mindre strukturerte enn SQL Server. Vanlige problemer:

Problem 1: Tomme rader og overskrifter Access-tabeller kan ha ekstra rader med formatering. Fjern dem i Power Query.

Problem 2: Feil datatyper Access Text-kolonner med tall iblandet. Rett opp i Power Query.

Problem 3: Relasjoner Access-relasjoner overføres ikke automatisk. Du må opprette dem manuelt i Model-visningen i Power BI.

Begrensninger med Access

Ingen automatisk oppdatering i skyen: Access-filer kan ikke oppdateres automatisk fra Power BI Service (skyen).

Løsninger: 1. Manuelt: Publiser på nytt fra Power BI Desktop når Access er oppdatert 2. Gateway: Installer Power BI Gateway på en datamaskin som har tilgang til Access-filen 3. Migrer: Flytt dataene til SQL Server eller SharePoint (anbefalt på lang sikt)

Vår anbefaling: Hvis du bruker Access aktivt, vurder å migrere til SQL Server. Vi hjelper virksomheter med dette jevnlig.

Midlertidig bruk: For mindre datasett (<50 000 rader) som ikke oppdateres ofte, fungerer Access fint med manuell publisering.

Power BI Gateway: Broen til on-premises

Hvis SQL Server- eller Access-databasen din ligger on-premises (i virksomhetens nettverk, ikke i skyen), trenger du en Power BI Gateway.

Hva er en gateway?

En gateway er et program som installeres på en datamaskin i nettverket deres. Den fungerer som bro mellom Power BI Service (skyen) og den lokale databasen deres.

Flyten: 1. Power BI Service ber om data 2. Forespørselen går til Gateway 3. Gateway henter data fra den lokale databasen 4. Gateway sender data til Power BI Service 5. Rapporten oppdateres

To typer gatewayer

On-premises data gateway (Standard mode): – For hele organisasjonen – Krever installasjon av IT-avdelingen – Kan brukes av alle rapportutviklere – Støtter flere datakilder samtidig

On-premises data gateway (Personal mode): – For én enkelt bruker – Enklere installasjon – Fungerer bare for den brukerens rapporter

Anbefaling: Standard mode for bruk i virksomheter.

Installer gateway (forenklet)

Steg 1: Last ned fra Microsoft (gratis)

Steg 2: Installer på en Windows-server eller dedikert datamaskin som: – Alltid er påslått – Har tilgang til databasen – Har stabil internettforbindelse

Steg 3: Logg inn med Power BI-konto

Steg 4: Registrer gatewayen i Power BI Service

Steg 5: Konfigurer datakilder (servernavn, autentisering)

IT-arbeid: 1-2 timer første gang. Deretter automatisk.

Ytelse: Beste praksis

Dårlig databaseytelse ødelegger brukeropplevelsen. Dette er hva vi har lært:

1. Filtrer tidlig og hardt

Dårlig: Importere 10 års data når du bare analyserer det siste året.

Bra: Filtrer i Power Query: WHERE Datum >= DATEADD(year, -1, GETDATE())

Resultat: 90 % mindre data = 10x raskere rapporter.

2. Bruk visninger for komplekse spørringer

Dårlig: Skrive kompleks SQL i Power BI med mange JOIN og subqueries.

Bra: Be DBA-en din opprette en visning på SQL Server. Power BI kobler seg til visningen.

Fordel: SQL Server optimaliserer spørringen. Lettere å vedlikeholde.

3. Partisjoner store tabeller

For datasett >5 millioner rader bruker du Incremental Refresh i Power BI.

I stedet for å oppdatere hele tabellen hver gang oppdateres bare nye/endrede rader.

Resultat: Oppdatering fra 2 timer til 10 minutter.

4. Indekser de riktige kolonnene

På SQL Server-siden: sørg for at kolonnene du filtrerer på (dato, kunde-ID osv.) har indekser.

Test: Hvis en SQL-spørring tar >5 sekunder i SSMS, blir Power BI tregt.

Kontakt DBA-en din for å legge til indekser.

5. Unngå SELECT *

Dårlig: SELECT * FROM Försäljning

Bra: SELECT OrderID, Datum, KundID, Belopp FROM Försäljning

Hent bare kolonnene du faktisk bruker.

Sikkerhet og Row-Level Security (RLS)

Når mange brukere deler rapporter, må du kontrollere hvem som ser hva.

Scenario: Salgsdashboard

Krav: – Salgssjefen ser hele landet – Regionsjefen ser sin region – Selgeren ser bare sine egne kunder

Løsning 1: Sikkerhet i SQL Server

Lag SQL Server-visninger med WHERE-klausuler basert på brukeren:

CREATE VIEW SäljareVy AS
SELECT * FROM Försäljning
WHERE Säljare = USER_NAME()

Power BI kobler seg til visningen. SQL Server håndterer sikkerheten.

Fordel: Sentralisert sikkerhet. Ulempe: Krever separate visninger for ulike roller.

Løsning 2: Row-Level Security i Power BI

Definer roller i Power BI Desktop:

Rolle: Selger DAX-filter: [Säljare] = USERNAME()

Rolle: Regionsjef DAX-filter: [Region] = LOOKUPVALUE(Användare[Region], Användare[Email], USERNAME())

Etter publisering tildeles brukerne roller i Power BI Service.

Fordel: Fleksibelt, styres i Power BI. Ulempe: RLS-regler kan bli komplekse.

Vår anbefaling: SQL Server-sikkerhet for enkle scenarier. RLS for komplekse eller dynamiske tilganger.

Vanlige problemer og løsninger

Problem: «Couldn’t connect to SQL Server»

Årsaker: – Feil servernavn (test i SSMS først) – Brannmuren blokkerer port 1433 – SQL Server godtar ikke eksterne tilkoblinger

Løsning: – Verifiser servernavn og port – Kontroller brannmurreglene – På SQL Server: Aktiver TCP/IP i SQL Server Configuration Manager

Problem: «Data source too large»

Årsak: Import-modellen overstiger 10 GB (grensen for Power BI Pro).

Løsninger: 1. Filtrer bort mer data i Power Query 2. Fjern unødvendige kolonner 3. Bruk aggregeringer (summer på SQL Server-siden) 4. Oppgrader til Power BI Premium (grense på 100 GB+) 5. Bytt til DirectQuery

Problem: «Gateway is offline»

Årsak: Gateway-maskinen er slått av eller har mistet tilkoblingen.

Løsning: – Kontroller at gateway-maskinen er påslått – Test internettforbindelsen – Start gateway-tjenesten på nytt – Oppdater gatewayen til nyeste versjon

Problem: «Refresh failed»

Årsaker: – Autentiseringsfeil (passordet er endret) – SQL Server midlertidig utilgjengelig – Timeout (spørringen tar for lang tid)

Løsning: – Oppdater credentials i innstillingene for datasettet – Øk timeout i gateway-innstillingene – Optimaliser SQL-spørringene for raskere kjøring

Neste steg

Du har nå grunnlaget for å koble Power BI til SQL Server og Access. Her er sjekklisten din:

For SQL Server: – [ ] Verifiser tilgang med SSMS – [ ] Bestem Import vs DirectQuery (Import for de fleste) – [ ] Koble til fra Power BI Desktop – [ ] Filtrer data i Power Query – [ ] Publiser og sett opp scheduled refresh – [ ] Installer Gateway hvis on-premises

For Access: – [ ] Koble til Access-filen – [ ] Rens data i Power Query – [ ] Bygg rapporten – [ ] Vurder migrering til SQL Server som langsiktig løsning

Optimalisering: – [ ] Filtrer data tidlig (Power Query) – [ ] Fjern unødvendige kolonner – [ ] Bruk SQL Server-visninger for komplekse spørringer – [ ] Test ytelsen og juster – [ ] Implementer sikkerhet (RLS eller SQL Server)

Trenger du hjelp?

Databaseintegrasjoner kan bli komplekse. Hos Excel Department har vi gjort dette hundrevis av ganger.

Vi kan hjelpe med: – Vurdere Import vs DirectQuery for deres behov – Sette opp Power BI Gateway – Optimalisere SQL-spørringer for ytelse – Implementere Row-Level Security – Migrere fra Access til SQL Server – Feilsøke tilkoblingsproblemer

Kontakt oss for en gratis konsultasjon (30 min).

Les mer: – Power BI for virksomheter: Komplett guide – SQL-koblet Excel – Access-konsulent

Kontakt Excel Department: – Telefon: 010-264 20 20 – E-post: info@exceldepartment.se – Adresse: Slottsbacken 8, 111 30 Stockholm