1 / 31

Neste generasjon Datavarehus med SQL Server 2008

Neste generasjon Datavarehus med SQL Server 2008. Tommy Strandvold. Pervasive Insight. Microsoft Business Intelligence Vision and strategy. Improving organizations by providing business insights to { all } employees, leading to better, faster, more relevant decisions.

tucker-trevino
Download Presentation

Neste generasjon Datavarehus med SQL Server 2008

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Neste generasjon Datavarehus med SQL Server 2008 Tommy Strandvold Pervasive Insight

  2. Microsoft Business IntelligenceVision and strategy Improving organizations by providing business insightsto { all } employees, leadingto better, faster, morerelevant decisions • Complete and integrated BI and performance-management offering • Widespread delivery of intelligence through Microsoft Office • Enterprise-grade and affordable

  3. Microsoft SQL Server 2008 Deliver • Deliver Relevant information • Drive Actionable Insights • Share insights Manage • Predictable Response • Simplified Management • Scale across mixed workloads Build • Integrate your data • Connect to any source • Develop visually

  4. The Data Warehouse topology with SQL 2008 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Data Profiling Integration Broker Data entry Master Data

  5. The Data Warehouse topology with SQL 2008 1 1 1 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Service Data Profiling Integration Broker Data entry Master Data 1

  6. The Data Warehouse topology with SQL 2008 2 1 1 1 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Archive Service Data Profiling Integration Broker Data entry Master Data 1

  7. The Data Warehouse topology with SQL 2008 2 1 1 3 1 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Archive Baseline Service Data Profiling Integration Broker Data entry Master Data 1 Data Errors

  8. The Data Warehouse topology with SQL 2008 1 1 1 3 4 2 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Archive Baseline DMSA Service Data Profiling Integration Broker Data entry Master Data 1 Data Errors

  9. The Data Warehouse topology with SQL 2008 2 3 1 1 1 5 5 5 4 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Archive Baseline DMSA Service Data Profiling Integration Broker Data entry Master Data 1 Data Errors

  10. The Data Warehouse topology with SQL 2008 2 5 4 5 3 5 1 1 1 Data Staging Area (DSA) Source systems BI Solutions Data Marts (DM) Enterprise Data Warehouse (EDW) Extract Archive BI front-end solution Baseline DMSA BI front-end solution Service BI front-end solution SQL Service Data Profiling Integration Broker BI front-end solution Data entry BI front-end solution Master Data 1 Data Errors 6 Service

  11. Agenda SQL Server Integration Services SQL Server Database Engine SQL Server Analysis Services

  12. SQL Server Integration Services Build • Data Profiling Task og Data Profile Viewer • ADO.NET source and destination • Source(nytt navn; het tidligere DataReader Source) • I SSIS 2005, DataReader Source måtte konfigureres via Advanced Editor og støttet kun SQL skrevet direkte inn i komponenten. • Destination • Ny komponent

  13. SQL Server Integration Services Build • Scripting med Visual Studio Tools for Applications • Fullintegrert IDE med debugging support • Fremdeles ikke debug i pipeline • Support for Visual C# .NET • Mulighet for å referere alle .NET assemblies

  14. SQL Server Integration Services Manage • Forbedret Performance og Caching i Lookup Transformation • 3 outputs • Grønn for lookup match, grønn for lookupfailure og rød for komponent error • Cache Mode • Bedre performance med precompiled scripts • Alle script må nå være pre-compiled! • Pipeline Performance • Paralellprossesering • Annet • Output configi Message window • Ny @[System::ParentContainer] variabel

  15. Agenda SQL Server Integration Services SQL Server Database Engine SQL Server Analysis Services

  16. SQL Server Relational DBMS Build • MERGE(Upsert) INSERT INTO edw.DimProsjekt(ParentDimKey, DimAlternateKey, fraDato) SELECT prosjektId, prosjektKode, getdate() FROM ( MERGE edw.DimProsjekt as EDW_prosjekt USING extract.Prosjekt AS src_delta ON (EDW_prosjekt.dimkey = src_delta.ProsjektId and EDW_prosjekt.TilDato is null) WHEN MATCHED THEN UPDATE SET EDW_prosjekt.TilDato = getdate() WHEN NOT MATCHED THEN INSERT VALUES (src_delta.prosjektId, src_delta.Prosjektkode, getdate()) OUTPUT $action, src_delta.prosjektId, src_delta.prosjektKode ) AS Changes(action, prosjektId, prosjektKode) WHERE action = 'UPDATE'; • Intellisence • Endelig!!!! • Change Data Capture • Hente endringer i et gitt tidsrom/siden sist • Er det en UPDATE, INSERT eller DELETE som har skjedd • Hvilke kolonner har UPDATE kjørt på

  17. SQL Server Relational DBMS Manage • Resource Governor CREATE FUNCTION rgclassifier() RETURNS SYSNAME WITH SCHEMABINDING AS DECLARE @grp_name AS SYSNAME IF (SUSER_NAME() = 'sa’) SET @grp_name = 'groupAdmin' IF (APP_NAME() LIKE '%Management Studio%’) SET @grp_name = 'groupAdhoc' IF (APP_NAME() LIKE '%Analysis Services%’) SET @grp_name = 'groupASProsessing' RETURN @grp_name • Star Join Query (Enterprise Edition) • Dynamisk bruk av Bitmap Filter/Bloom Filter Algoritme www.wikipedia.org/wiki/Bloom_filter

  18. SQL Server Relational DBMS Manage • Data Compression • ROW compression • fixed datatyper blir konvertert til variable length • PAGE compression • column prefix og page level dictionary • Backup Compression • Minimally logged INSERT

  19. Agenda SQL Server Integration Services SQL Server Database Engine SQL Server Analysis Services

  20. SQL Server Analysis Services Build • Attribute Relationship Designer • Enhanced Dimension and Aggregation Designers • Best Practice Design Alerts • BIDSHelper • www.codeplex.com/bidshelper

  21. SQL Server Analysis Services Build BIDS Demo

  22. MDX Syntax Extensions Build • CREATE KPI • Definerer KIP’er i MDX script eller som en Session KPI • CREATE KPI CURRENTCUBE | Cube_Name .KPI_Name AS KPI_Value [, FORMAT_STRING=string, VISIBLE=value, NON_EMPTY_BEHAVIOR=<measure or set>, CAPTION=string, DISPLAY_FOLDER=string, ASSOCIATED_MEASURE_GROUP=<measure group>] • UPDATE MEMBER • Endre deler av en kalkulert medlem uten å endre alle egenskaper • UPDATE MEMBER Cube_Name.Member_Name as mdx_expression [, Property_Name = Property_Value,…n]

  23. MDX Syntax Extensions Build • CREATE MEMBER • CREATE [ SESSION ] [HIDDDEN] [ CALCULATED ] MEMBER CURRENTCUBE | Cube_Name.Member_Name AS MDX_Expression [,Property_Name = Property_Value, ...n] [, CAPTION=string, DISPLAY_FOLDER=string, ASSOCIATED_MEASURE_GROUP=<measure group>]......[,SCOPE_ISOLATION = CUBE] • CREATE SET • CREATE [SESSION] [STATIC | DYNAMIC][HIDDEN] SET CURRENTCUBE | Cube_Name .Set_Name AS 'Set_Expression'[, CAPTION=string, DISPLAY_FOLDER=string]

  24. Static Versus Dynamic Sets Build • Static Set • Kalkuleres ved evaluering av MDX’en • Tar ikke hendsyn til subcube/where • Dynamic Set • Kalkuleres i kontekst av subcube/where

  25. SQL Server Analysis Services Manage Scale out Analysis

  26. SQL Server Analysis Services Manage • Block Computation • Kalkulerer og beregner kun non-null verdier i en MDX spørring • Celle navigering gjøres kun en gang • PrevMember, LastMember, FirstChild, Parent osv. • Block Computation er verdt oppgradering til SQL 2008 alene • Har størst effekt på kuber med mye null verdier

  27. Cell by Cell Computation Manage (Measures.[Sales Amount], Product.[Product Categories].Parent Measures.[Sales Amount] / Measures.[Product Contribution] =

  28. Block Computation Logic Manage • Fakta • A = Product.CurrentMember • B = Product.Parent • Bare når A er not null, A / B er not null • B erkonstant • Storage Engine henter bare non-null data • Konklusjon • Kjører kun kalkuleringnår teller-verdienereturneresfra Storage Engine ognevnerhar en konstantverdi

  29. Block Computation Manage Steg 1: Hent non-null verdierfra Storage Engine (Measures.[Sales Amount], Product.[Product Categories].Parent Measures.[Sales Amount] Steg 2: Kalkuler Steg 3: Legg til null verdieriresultatet Measures.[Product Contribution]

  30. Oppsummering Bedre ytelse i ETL SQL 2008 gjør det mye enklere å ha et real-time datavarehus enn tidligere Bedre AS ytelse med Block Computation Enklere å forsikre seg om at kuben er optimal

More Related