Category Archives: Tips & Tricks

Nyt liv til Reporting Services, part 2

Netop hjemvendt fra PASS Summit konferencen er det på sin plads med en opfølgning på “Nyt liv til Reporting Services“, da der kom rigtig mange nye detaljer på bordet angående både SQL Server 2016 Service Pack 1 og den næste release (indtil videre kendt som vNext).

ssrs_teamDer var rigtig god stemning omkring Reporting Services med super god deltagelse fra teamet bag produktet. De var tilsvarende meget åbne og klar på at modtage input og svare på spørgsmål. Både i form af synlighed på SQL Clinic og aktivitet på Twitter hvor de den ene dag inviterede til meetup på en lokal bar og den næste til fokusgruppe i frokostpausen. Desuden var de to Product Managers, Riccardo Muti og Christopher Finlan meget aktive med blog indlæg, hvor det blev til 5 styk samlet på https://blogs.msdn.microsoft.com/sqlrsteamblog/ og https://christopherfinlan.com/

Som omtalt i mit andet blog indlæg, så blev der på Ignite konferencen vist et sneak-peak af Power BI via Report manageren. På PASS Summit gik man så skridtet videre og havde næsten en helt session (SQL Server Reporting Services: What’s New and What’s Next) kun om dette. Derudover blev “Technical Preview” annonceret – det er simpelthen blevet muligt at starte en helt klar og konfigureret Azure VM op, så man kan prøve det selv. Og hvis man har lidt teknisk snilde kan den downloades og mountes i ens eget miljø med de frihedsgrader det nu giver :-)

ssrs_vm

Preview’et indeholder ud over den integrerede on premise Power BI også en del nye features som kommer i SQL Server 2016 Service Pack 1. Her får vi bl.a.

  • List view in portal
  • Expanded context menu when you click the ‘…’ option
  • Direct url navigation for KPI’s

Det rygtes at SP1 bliver klar til download her i november. Vi kommer desværre til at vente noget længere på Power BI, da det først kommer i næste release… Man havde nok forventet det noget tidligere, når vi nu har et preview som virker. Godt nok “kun” med live connection til SSAS kuber, men det er jo også alt hvad vi har behov for nu og her. Jeg vil være meget ked af, hvis udgivelsen udskydes pga. manglende Power Query og deraf manglende Self-Service muligheder. Vi har behov for “Analytic Enterprise BI Reporting”, hvilket jeg da også fik fortalt de to product managers. Men min lille stemme vægter nok ikke så meget. Derudover mangler vi også Custom Visuals, men det kunne jeg også leve uden i første version.

En anden interessant ting er, at Microsoft overvejer at adskille Reporting Services fra resten af SQL Serveren, hvilket giver rigtig god mening. Et separat download som dermed kan følge sin egen hurtigere “release cycle” som de også har gjort med SSDT og SSMS. Når man tænker over det, så er der faktisk ikke rigtig nogen vigtig teknisk binding udover ReportServer databasen som, allerede nu, sagtens kan ligge på en tidligere SQL Server version. Det virkelig interessante er hvordan licenseringen vil blive, hvilket Microsoft ikke vil komme ind på. De vil heller ikke hverken be- eller afkræfte om Power BI i Reporting Services kommer til at koste ekstra.

Over and out fra justB, som virkelig er optimistisk for fremtiden for Reporting Services!

Reporting Services ressourcer

Som opfølgning på et Reporting Services kursus jeg netop har afholdt kommer her en samling af gode ressourcer:

Der findes helt sikkert mange flere rigtig gode ressourcer. Dette var bare dem jeg lige kunne komme op med. Hvis du er interesseret i at komme på kursus i Reporting Services, så skulle du tjekke mit kursus ud, som jeg holder hos Orange Man: http://orangeman.dk/kursuskatalog/Reporting-Services/

Find hvilke rapporter der anvender embedded connection

Var ude ved en kunde i den forgange uge, som netop har fået ny DWH server. Jeg har gennem nogle år hjulpet dem med at opsætte en SharePoint BI løsning, hvor der anvendes en god blanding af Reporting Services, Excel Services og PerformancePoint Services. Skiftet til den nye DWH server har været problemfrit, da der blev opsat et alias på navnet fra den gamle server, som peger på den nye server. Udfordringen er nu, at kundes IT afdeling vil fjerne aliaset igen…

Så nu skal vi sikre at alle rapporter, dashboards m.m. bliver peget over på den nye server. Nemt nok med PerformancePoint Services, da det kun er muligt at oprette shared connectionsog disse så blot skal ændres. Også nemt med Excel Services, da vi her har opsat at der kun tillades “external data connections” og Excel rapporterne således ikke virker med “embedded data connections”. Samme mulighed findes desværre ikke med Reporting Services, så hvordan finder man rapporter der anvender embedded connections og dermed peger på det gamle server navn?

ReportServer databasen indeholder alle rapport definitioner (rdl-filer) i catalog tabellen. Det er gemt i binært format i content feltet:

CatalogTabel

Kan du finde rapporten med en embedded connection? Nej vel. Tricket er at konvertere det binære format om til XML og så udsøge det element, som viser om der anvendes en embedded eller shared connection. Det er ikke lige min spids kompetence, men Google kan som altid hjælpe. Bret Stateham har skrevet et godt og grundigt blogindlæg: http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/

Eksemplet går på at udtrække selve den query der anvendes i rapporterne. Rigtig rigtig smart, men ikke lige det vi har brug for. Det er bare at tage et kig på XML’en i en rapport med embedded connection og se hvad elementerne hedder:

ConnectionStringXML

Så skal sidste del af scriptet blot ændres til at udsøge DataProvider og ConnectString:

ConnectionStringSQL

Da disse elementer kun findes i rapporter med embedded connection, vil vi kun få listen over de rapporter der skal ændres. Ved den pågældende kunde var der ca. 20 rapporter, hvor vi så nemt kunne ændre til at de anvendte de shared connection der var opsat og var ændret til at pege på den nye server.

ConnectionStringResult

Det var da smart :-)

Automatisk testning af kuber

Jeg havde lørdag d. 29. marts fornøjelsen at præsentere mit indlæg om automatiseret test af kuber på SQLSaturday #275, der blev afholdt i Microsofts lokaler i Hellerup. Et helt fantastisk arrangement, som jeg håber kan blive en tradition.

Mit indlæg har rod i en konkret kundeopgave, hvor der var behov for at automatisere test af en forholdsvis stor og kompleks kube, som var en del af et udviklings-setup med release hver tredje uge. Helt simpelt har jeg udviklet en metode til at udtrække snapshot af både kubens struktur (metadata) samt selve indholdet (data). Snaphot kan således fortages både før og efter deployment og derefter sammenlignes. Det er dog også muligt at sammenligne på tværs af miljøer eller henover tid ved f.eks. at foretage snapshot dagligt.

Kubens metadata eksponeres via en række DMV’er – f.eks. giver “SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES” en liste over alle measures i kuben med et utal af properties. Ved at opsætte en LinkedServer kan det via en OpenQuery forespørgsel relativt simpelt gemmes i en tabel og wupti, så kan der opsættes en rapport som synligøre forskelle mellem to forskellige snaphot. Tilsvarende kan opsætte via andre DMV’er, så metadata for dimensioner, attributter, measuregroups m.m. kan sammenlignes.RapportMetadataSamme metode med OpenQuery og LinkedServer kan anvendes til udtræk af data, som dog kun kan blive et udsnit – men hvordan udvælges et repræsentativt udsnit? Og hvordan håndteres, at data helt naturligt ændrer sig over tid? I min konkrete opgave valgte jeg at udtrække data for alle measures over flere forskellige tidsperioder (32 dage, 13 måneder og 5 år) filtreret på en anden gennemgående dimension. MDX forespørglen er opsat forholdvis dynamisk med flere forskellige parametre – f.eks. med en offset dato, så der kan udtrækkes data tilbage i tiden. Listen over measures i kuben kan fåes via enten metadata snapshot eller via “Measures.AllMembers” – bemærk dog at Measures.AllMembers kun giver de synlige measures og at en liste via metadata kan blive meget lang og dermed få forspørgslen til at overstige begrænsningen på 8000 tegn.

Via en række linked rapporter kan eventuelle forskelle mellem snapshot synliggøres og om der findes et mønster – er det kun enkelte measures og/eller på konkrete tidsperioder?RapportTil demo for mit indlæg har jeg opsat eksempel på AdventureWorks kube databasen deployed på to forskellige instanser, hvor jeg har jeg ændret i både struktur og indhold i kuberne. Jeg har uploaded både slides og demo-filerne, så I selv kan afprøve metoden. Jeg har kun tilpasset et udsnit af min kundeopgave til at virke på AdventureWorks, så der er uden tvivl behov for at tilpasse og udvide, hvis det skal anvendes professionelt :-)

Foruden min egen udviklet metode, har jeg også med succes anvendt Microsoft BI test frameworket NBi, som har sin klare styrke i at teste dele af kuben. Det er en udvidelse til NUnit, hvor der kan opsættes test-cases via xml-syntax – f.eks. teste antal af members i en specifik attribut, performance på afviklingen af en specifik mdx eller sammenligne små resultatsæt. En anden styrke er at der kan opsættes tresholds – f.eks. at antallet af members skal ligge mellem 15 og 25 eller værdien af et measure skal være mellem 0 og 1. Perfekt til at teste at de ændringer man ligger i produktion er slået rigtigt igennem.

Begge disse løsninger komplementere hinanden godt, men kan også fint anvendes uafhængigt. Tjek begge ud og se hvad der passer i dit/jeres setup.

Self-Service BI ressourcer

Som opfølgning på et tre dages kursus jeg afholdte i sidste uge, har jeg samlet links til en række yderligere ressourcer. Kurset var overordnet opdelt i en dag med Report Builder og to dage med Excel, hvor den første dag havde fokus på data modellering og den næste på præsentation/rapportering.

Power Pivot:

DAX:

Power Query:

Reporting Services:

Dette er på ingen måde udtømmende, men en god start, hvis man skal arbejde videre med Microsoft Self-Service Business Intelligence. Kan personligt anbefale alle ressourcerne :-)

Hvis du har en god ressource, så smid den i en kommentar. Tak!

Automatisk kube dokumentation i Reporting Services

Dette indlæg har stået på min liste i snart tre år, så nu er det vidst på tide jeg får det skrevet. Alle Microsoft BI udviklere har nemlig krav til denne information, da vi som udgangspunkt alle hader at dokumentere og elsker at automatisere trivielle opgaver :-)

Skal skynde mig at tilføje, at alt kredit går til Alex Whittles fra det engelske BI konsulentfirma Purple Frog. Jeg deltog tilbage på SQLBits VIII til hans session om “Automating SSAS cube documentation using SSRS, DMV and Spatial Data“, hvorefter jeg begyndte at bruge den kode han lagde tilgængelig på sin blog. Har implementeret det med stor succes ved en håndfuld virksomheder.

Det går i alt sin enkelthed ud på at trække metadata ud af kuben via de DMV’ere som er tilgængelige, hvorefter det kan præsenteres i en række SSRS rapporter, som er indbyrdes linket sammen. Som en cool detalje er “Dimension Usage” vist grafisk i stjerne skema – tegnet via den map engine der ligger i SSRS. Der er nok ikke mange, som er klar over, at det faktisk er en tegne motor. Allan har skrevet et mere detaljeret indlæg om dette emne “Drawing a logo or diagram using SQL spatial data“.

JavascriptJeg har foretaget en række udvidelser af udtræk og rapporter, hvor den største er muligheden for at tilknytte kommentarer til de forskellige elementer direkte via rapporterne. Der er til hver objekt indsat et lille edit-ikon, som via en action og lidt javascript åbner en lille rapport i et pop-up vindue.

MeasureListe

Det som angives i parameter-input bokse bliver via en stored procedurer gemt i en tabel ved klik på “View Report”. Der anvendes et merge-statement, så det er muligt også at foretage rettelser.

InputWindow

Indrømmet – det er lidt en høker løsning, men det er smart at have præsentation og indtastning samlet i den samme “applikation”. Alle objekter i kuben har unikke navne, så det er meget simpelt at gemme i beskrivelser af dimensioner, attributter, measures m.m. i den samme tabel. Har i et tilfælde udvidet, så der udover beskrivelse, reference id og link også er mulighed for at angive den primære kilde – herunder kildesystem, tabel og felt.

Når jeg har præsenteret denne løsning har flere foreslået, om jeg ikke skulle lave noget write-back til kuben, så beskrivelserne ikke kun er tilgængelige via rapporterne. En rigtig god ide, som burde kunne laves via lidt xmla. jeg har dog valgt ikke at gå videre med det, af den simple grund, at Excel ikke formår at vise “Descriptions” fra measures.

Af andre udvidelser jeg har lavet, kan nævnes visning af “attribut properties” under dimensionerne. Endvidere har jeg i BUS matice og stjerne-skemaer tilføjet markering af mange-til-mange relationer. Denne type relation kan være en virkelig dræber for query performance og derfor rar, at være lidt opmærksom på.

SjerneskemaM2M

En anden lille detalje er tilføjelse for mulighed for at skjule/vise “hidden” objects, som i nogle tilfælde er fine at kunne se og andre tilfælde bare støjer. Derudover har jeg i rapporten med measures taget de expressions der angiver aggregering- og data type og ført tilbage i stored procedurer.

Slutteligt kan nævnes, at jeg netop nu sidder med et projekt, hvor jeg gemmer de meta-data der udtrækkes, så det er muligt at sammenligne kubens udvikling over tid samt danne sig et overblik over forskelligheder mellem miljøer. konkret handler det om at foretage automatisk unit test af kuber, hvilket jeg holder indlæg om på MsBIP møde nr. 18 og SQLSaturday #275.

Microsoft BI VPC – del 1, opret VHD og boot

Dette er første del ud af tre, som omhandler hvordan man selv kan opsætte en komplet VPC med alle Microsofts nyeste BI værktøjer – herunder SharePoint 2010 og SQL Server 2012. Dette blog indlæg fokusere alene på opsætning af selve den virtuelle harddisk og installation af Windows Server 2008 R2. De næste to blogindlæg i rækken vil omhandle henholdsvis installation og konfiguration af værktøjerne.

Første step er at oprettet selve VHD’en, hvilket meget nemt kan gøres via “Disk Management Console”, som kan startes via diskmgmt.msc eller findes som et “snap-in” under Microsoft Management Console. Jeg kører en dansk version af Windows 7, så her hedder det henholdsvis Diskhåndtering og Computeradministration. Jeg beklager at screen-shoots er på dansk, men i overlever jo nok… Vælg “Actions” og “Create VHD”. Vælg herefter en placering og navn, samt størrelse. Mine erfaringer siger at 35-40 GB er en god størrelse. Det er muligt efterfølgende at ændre størrelsen via 3. parts værktøjet VHD Reziser. Vælg som anbefalet “Fixed size” – så er pladsen allokeret på disken.

Herefter er næste punkt at initalisere disken, hvilket kan klares med et højreklik og “Initalize Disk”. Lad MBR (Master Boot Record) være klikket til. Det tager et split sekund

Så skal der oprettes en ny diskenhed, hvilket igen kræver et højreklik efterfulgt af Next, Next, Next.

Efter de tre gange next er vi kommet frem til formateringen, hvor vi lige kan give enheden et navn inden det næste Next og et afsluttende klik på “Finish”.

Vupti – så er der oprettet en ny VHD, som hermed er klar til installation af Windows Server 2008 R2.

Næste step er her hvor det rigtige smarte kommer – nemlig hvordan man kan “smide” en installation direkte ned på en VHD via det lille Install-WindowsImage PowerShell script. Det er selvfølgelig en forudsætning at man har en ISO af Windows Server 2008 R2 samt en aktiveringskode, hvilket i mit tilfælde er tilgængeligt via en MSDN Subscription.

Først skal vi have mounted ISO filen, hvor jeg har fin erfaring med at anvende Deamon Tools Lite, men der findes også andre tools. Er man helt old school så kan man selvfølgelig også brænde filen ud på en DVD :) Så skal vi browse os frem og finde stien til “install.wim” filen, som normalt ligger under \Sources\install.wim

Hvis ikke allerede du har downloaded Install-WindowsImage PowerShell script, så er det nu det skal gøres. Via linket kan også findes yderligere dokumentation. Det er vigtigt at PowerShell køres med admin rettigheder, så husk “Run as Admin” når du starter det op. Først skal vi have ændret en policy, hvilket klares med: “set-ExecutionPolicy Unrestricted” efterfulgt af Y. Derefter kan vi via “.\Install-WindowsImage.ps1 -WIM D:\sources\install.wim” finde frem til hvilke versioner vi kan installere med tilhørende ID’er. Jeg vil anbefale at der vælges Enterprise (Full Installation) som i mit tilfælde har index 3. Sidste punkt er så at få angivet hvor installationen skal “smides”, som er den VHD vi tidligere oprettede. I mit tilfælde er det så V-drevet: “.\Install-WindowsImage.ps1 -WIM D:\sources\install.wim -Apply -Index 3 -Destination V:”

Som det kan ses på screen-shot så tog installationen i mit tilfælde kun 3½ minut – det er da meget godt gået :)
Nu kunne vi for så vidt godt anvende f.eks. VirtualBox til at boot op på VHD’en, men jeg vil her vise, hvordan vi konfigurerer native boot. Her findes der i Windows 7 de to cmd tools bcdboot og bcdedit. Start en command-prompt op og kør “V:\Windows\System32\bcdboot V:\Windows” for at oprette startfiler. Herefter skal vi oprette en entry i boot loaderen, hvor vi starter med at få fat på det GUID der passer med vores nye VHD – kør: “bcdedit -v”
Når vi nu har fået fat på den rette GUID, så skal vi have den ændret beskrivelsen, så den har et passende navn i boat loaderen: “bcdedit /set {fdfb7391-baa9-11e0-8326-cc52af81fd63} description “SharePoint BI””
Herefter kan vi ændre så vi ikke har default boot på den nye VHD: “bcdedit /default {5bc62211-8588-11e0-997b-f0def1643ed3}”
Til slut er er der blot at genstarte maskinen og se om det hele virker som det skal :-)
Næste blog indlæg i rækken får titlen: “Microsoft BI VPC – del 2, installation”. Håber I har tålmodighed til det bliver skrevet – ellers kan I jo også bare kaste jer ud i det!

SSAS performance testing

En hurtig blog-post som opfølgning på mit “SSAS performance testing” indlæg på MsBIP møde nr. 1, hvor jeg gennemgik forskellige tools til at hjælpe ved en flerbruger test. Overordnet set handler det om at fyre en masse MDX’er afsted mod kuben og så samtidigt foretage forskellige målinger – herunder eksekveringstiden af hver MDX og anvendelses af systemressourcer på serveren. Ved at foretage dette struktureret kan der opsættes en query baseline, hvis resultat skal kunne reproduceres. Dette kan bruges når man laver ændringer på kuben, da man så kan måle om disse har et negativt eller positivt resultat.

Som en del af min værktøjskasse har jeg med succes anvendt “Scripts for Using ASCMD for Stress Testing” og “AS Performance Workbench“. Begge er tools fra CodePlex og dermed udviklet af “nørder” som os selv :-D

ASCMD og tilhørende scripts er helt sikkert den mest nørdede tilgang, men dermed også den som bedst kan tilpasset lige præcis dine behov. Det er næsten kun fantasien der sætter grænsen for hvad og hvordan der kan måles. Jeg kan anbefale at kigge på -T og -Tl duration parameterne i ASCMD dokumentationen

AS Performance Workbench er udviklet som en casestudy til SSAS Mastro programmet og er nemt og hurtigt at komme i gang med. Det giver nogle fine grafiske rapporter, men har også nogle begrænsninger. F.eks. kan der ikke afvikles queryes der tager over 30 sekunder og der kan kun være en query i hver fil, modsat ASCMD som godtager flere MDX’er i hver fil, hvis de adskilles af “GO”.

Sidst vil jeg lige fremhæve vigtigheden af at clear cachen ved disse test – herunder også filesystem cache, som mig bekendt kun kan ryddes med brug af Analysis Services Stored Procedure Project (ASSP).  Begge ovennævnte tools anvender ASSP.

Monitorering via Profiler, PerfMon og Xperf må komme med i en fremtidig blogpost…

Publisering fra Excel til SharePoint

Med udgangspunkt i mit indlæg på ExcelBI møde nr. 3 for et par uger siden kommer her et opfølgende blog indlæg, hvor jeg kort vil opsummere hvad der blev præsenteret. Emnet var en lidt mere overordnet ikke teknisk introduktion til Excel Services – altså ikke noget med Secure Store Services, Trusted Locations, Excel Web Access, XLViewer eller REST API.
 
Første punkt var en helt lavpraktisk gennemgang af “Publish Options”, hvor der kan vælges mellem “Entire Workbook”, “Sheets” og “Items in the Workbook” Specielt sidste mulighed er ufattelig smart, hvis man gerne vil embed i SharePoint uden at det ligner Excel alt for meget, men mere om det senere.
Selecting worksheets or items
Andet punkt på agendaen var integrationen i SharePoint, hvor der efter min mening af tre muligheder:
  • Full page er den dovne løsning, hvis det skal gå meget hurtigt eller man er ukritisk i forhold til designet. Tilstanden aktiveres ved at klikke på et Excel ark i et dokument library. En simpel og hurtig løsning.
  • PerformancePoint Services Dasboard som rapporterings skal, hvor Excel Services kombineres meget effektivt med filtre fra PPS via Dashboard Designeren. Det kan lyde lidt komplekst, men det er faktisk utrolig nemt når man lige får det prøvet. Det helt væsenlige er at få opsat sine parametre rigtigt, så der kan skabes et eller flere link fra PPS filtre til Excel ark. Se dette eksempel på hvordan der via et Time Intelligence filter i PPS styres hvor lang periode en Spark Line i et Excel ark skal løbe over. Det er stort set kun fantasien der sætter grænsen for hvad og hvordan der kan linkes, da der kan laves meget formel gymnastik i Excel.
  • WebPart page er faktisk hvad der skabes hvis der oprettes et Dashboard i PPS, men det er også muligt at sammensætte selv direkte i SharePoint. Denne løsning kan specielt være nødvendig at anvende, hvis dit “dashboard” skal indeholder andre elementer der ikke findes i PPS så som et dokument library.


Sidste punkt på dagsordenen var “Tips og tricks” og lidt opsamling på de demonstrationer der blev vist undervejs. Et helt simpelt råd er at huske og fjerne/skjule gridlines, med mindre man er helt vild med at det skal ligne Excel. Husk desuden at anvende “Named Items” – ikke kun til parametre, men også hvis der kun skal publiseres et bestemt område. Marker helt simpelt f.eks. celle B2:D8 og giv området et navn. Derved kan layoutet styres bedre og det er muligt at gemme yderligere logik i de celler der ikke er markeret uden at det vises til brugerne. Anvend “Name Manager“, hvis du skal have overblik eller ønsker at området skal defineres dynamisk udfra f.eks. input værdier.

Helt underforstået så forudsætter jeg at datakilden altid er en SSAS kube når der publiseres fra Excel til SharePoint. Derfor vil jeg gerne slå et stor slag for “Cube functions”, som bliver helt uundværligt når først man er begyndt at anvende det. Specielt i relation til Excel Services er det ekstra kraftfuldt, da pivot tabeller og chart godt kan blive lidt ensformige og kedelige. Ved at anvende cube functions kan man forholdsvis enkelt lave en top 3 liste som den ovenfor og meget andet smart. En god måde at komme i gang er at lave en pivot tabel og vælge “OLAP Tools” –> “Convert to Formulas”. Som navnet angiver omdannes pivot tabellen til formler og man kan så aflurer nogle af funktionerne.

Det sidste tip går på at oprette og anvende “Named sets“, som er en feature der gælder pivot tabeller hvor ved der kan oprettes simple grupper der kan genanvendes eller blot asymmetriske hierarkier. Hvis man er hård til MDX kan de også skrives den vej igennem.