Business Intelligence

Creazione di un Foundation di dati per una soluzione di Business Intelligence

Derek Comingore

Download codice disponibile in: SampleCode2009_08.exe(151 KB)

Informazioni di riepilogo:

• Segue il modello dimensionale per creare un data mart
• Lo sviluppo di processi ETL e dati esegue il mapping
• Utilizzo di Integration Services per ETL
• Creazione di un progetto di Integration Services in BIDS

Contenuto

Informazioni sui requisiti
Utilizzare il modello dimensionale
Creazione di una matrice di bus
Creazione di una mappa di dati
Creazione di data mart
Distribuzione di data mart
Lo sviluppo di processi ETL
Creazione di un progetto SSIS in BIDS
Creazione di connessioni dati comuni
Sviluppo ETL per dimensioni
Definizione di estrazione e caricare un flusso di dati
Lo sviluppo di pacchetti aggiuntivi
Lo sviluppo il pacchetto di tabella dei fatti vendite Internet
Passaggi finali

I dati sono la parte più importante di qualsiasi soluzione di business intelligence (BI). Come spiegato in Stacia Misner " Pianificazione della soluzione di BI Microsoft prima"recupero dei dati in una soluzione BI e gestione di una volta presente implica diversi passaggi. Professionisti BI fare riferimento alla procedura: estrarre, trasformare e caricare i processi (ETL). Anche se non si prevede di adottare una carriera incentrata sulla Business Intelligence, è possibile comunque sfruttare ETL tecniche e strumenti per gestire i dati che è necessario fornire le attività quotidiane decisioni del processo. In questo articolo viene illustrato come progettare e creare un semplice data mart per illustrare come è possibile utilizzare SQL Server 2008 Integration Services (SSIS) per eseguire ETL per la propria soluzione di BI.

Informazioni sui requisiti

Come per qualsiasi progetto IT, il modo migliore per avviare un progetto ETL è per comprendere i requisiti generali della soluzione di Business Intelligence che si desidera creare e quindi la funzionalità ai dati per determinare il modo migliore per supportare tali requisiti. Il primo articolo della serie imposta la fase per la società fittizia Adventure Works deve descrivendo i requisiti analitici come diverse domande, che l'azienda deve rispondere la soluzione di BI. Queste domande evidenziare la necessità di Adventure Works comprendere le vendite di prodotti da diverse prospettive: redditività canale di distribuzione (rivenditori o Internet), variazioni della domanda per i prodotti nel tempo e la differenza tra le vendite effettive previsioni e vendite per prodotto, venditore, area geografica e tipo di rivenditore. Le risposte a queste domande consentirà di Adventure Works decide quali canale di distribuzione la società deve concentrarsi su per aumentare i profitti come adattare i processi di produzione per soddisfare meglio richiedono e come le modifiche in strategia di vendita possono essere utili della società di soddisfare gli obiettivi di vendita. Verrà visualizzato come i dati supportano la risposta alle domande dei business quando si aggiunge SQL Server Reporting Services (SSRS) per la soluzione di BI.

Prima di iniziare la progettazione di data mart per Adventure Works tenendo presenti questi requisiti, si desidera il modello necessario le informazioni dal punto di vista aziendale. Progettazione del data mart in altre parole, deve essere basata su quella che degli utenti richiederà le proprie domande anziché quella dei dati proviene da origini dati.

Sarà necessario il database di esempio Adventure Works OLTP di SQL Server 2008 per gli esempi di codice, consultare questo articolo.

Utilizzare il modello dimensionale

Un data mart in genere viene creato utilizzando una struttura modello dimensionale, che è uno schema di database adatto per l'analisi. (È una grande risorsa per imparare a utilizzare la modellazione dimensionale kimballgroup.com.) Un modello dimensionale presenta i dati in modo del familiare agli utenti, e consente di creare strutture di dati ottimizzati per la ricerca di volumi elevati di dati. È possibile ottenere questa ottimizzazione denormalizzazione dati, che consente di selezionare rapidamente e in modo efficiente aggregare una grande quantità di dati in fase di query il motore di database. Nello schema denormalizzato per la soluzione Adventure Works, verranno includere due tipi di tabelle: dimensione e fatto. Una tabella delle dimensioni contiene informazioni su entità aziendali e oggetti, ad esempio prodotti o i rivenditori. Una tabella dei fatti, si utilizzerà per i dati numerici di vendita è necessario aggregare, contiene chiavi fatti alle tabelle di dimensioni e misure numeriche. Spiegherò più sulle tabelle dei fatti più avanti in questo articolo.

È possibile implementare le tabelle di un modello dimensionale in due tipi di schemi: stella e a fiocco di neve. In termini semplici, uno schema a stella utilizza una tabella per ogni dimensione, che a sua volta una query fa riferimento a una tabella dei fatti con un unico join. Uno schema a fiocco di neve utilizza due o più tabelle per ogni dimensione e pertanto richiede più join in una query per visualizzare tutti i dati. Questo insieme di CSS join significa che le query spesso eseguita più lentamente in uno schema a fiocco di neve più in uno schema a stella. Ai fini di questo articolo, si potrà semplificare la progettazione e uno schema a stella.

Creazione di una matrice di bus

Durante il processo di modellazione dimensionale, È possibile creare una matrice di bus per identificare le dimensioni associate vendite, lo stato attivo della soluzione di BI per Adventure Works. Tenere presente che due canali di vendita AdventureWorks: wholesale vendite per rivenditori e singole vendite realizzate tramite Internet. Inoltre possibile utilizzare la matrice di bus per identificare la relazione di ciascuna dimensione a uno o entrambi tipi di vendita. Figura 1 Mostra la matrice del bus di esempio per la vendita di Adventure Works.

Il passaggio successivo consiste nel determinare le misure per la soluzione. Le misure sono i valori numerici necessari per l'analisi. Questi possono provenire direttamente dall'origine, ad esempio dollari di vendite o i costi del prodotto, oppure derivati tramite un calcolo, ad esempio moltiplicando una quantità per un importo in dollari per un volume di vendite estesa. È inoltre necessario decidere quali attributi da includere ogni dimensione. Gli attributi sono i singoli elementi in una dimensione, corrispondente a colonne di una tabella, che è possibile utilizzare a dati di gruppo o di filtro per analisi, ad esempio paese nella dimensione area vendite o anno nella data dimensione. Non in dettaglio ogni misura identificato o di un attributo dimensionale in questo articolo, semplicemente tenere presente che il processo di identificazione deve essere eseguita.

Creazione di una mappa di dati

Prima di creare tabelle fisiche del data mart, È necessario eseguire un'ulteriore pianificazione. In particolare, È necessario creare un documento di mappa di dati per associare ogni colonna di destinazione nello schema di data mart di dati alle colonne nel sistema di Adventure Works OLTP di origine (il database AdventureWorks2008 che è possibile scaricare e installare come descritto nell'articolo di Stacia Misner su p. 31). È possibile utilizzare diverse applicazioni per creare una mappa dati. Il formato non è importante quanto il contenuto. Mi piace sviluppare mappe dati in Microsoft Office Excel. Figura 2 Mostra la scheda DimProduct creato con la mappa dati. Creato anche dati DimCustomer e FactInternetSales esegue il mapping. Ogni foglio della cartella di lavoro rappresenta una delle tabelle del data mart. Su ogni foglio, È necessario semplicemente due colonne: uno per la colonna di origine e uno per la colonna di destinazione.

Nella figura 2 scheda di mapping dati di DimProduct

Ogni tabella delle dimensioni (ad eccezione di dimensione data) ha una chiave primaria che nota come chiave surrogata (in genere una colonna di identità). Uno dei vantaggi dell'utilizzo di chiavi surrogate è che è possibile combinare dati provenienti da più sistemi senza il rischio di chiavi duplicate.

Le tabelle delle dimensioni inoltre disporre di una colonna di chiave alternativa. Queste chiavi alternative rappresentano chiavi naturale, detta anche chiavi business. Questi sono gli identificatori del sistema di origine. La colonna di CustomerAlternateKey nella dimensione utente viene associata, ad esempio, il campo del database OLTP di Adventure Works AccountNumber nella tabella Sales.Customer. Queste chiavi è archiviato nella tabella delle dimensioni, è in un modo possibile far corrispondere i record già la dimensione con i record estratti dall'origine ogni volta che si esegue un processo ETL per ogni dimensione.

Quasi ogni data mart include una dimensione di data perché analisi business confronta spesso modifiche di misure per data, settimana, mese, trimestre o anno. La dimensione di data è quasi mai deriva dalla finestra di un sistema di origine, in modo che non si applicano i motivi per cui utilizzando una chiave di SQL Server IDENTITY–based. Si utilizzerà invece di ciò che viene denominata chiave smart con il formato aaaammgg memorizzato come una colonna integer di SQL Server. Una chiave smart è una chiave generata dalla logica o script come invece che da una chiave con incremento automatico ad esempio una colonna IDENTITY in SQL Server.

Tenere presente che la dimensione di data non è in genere associata a una tabella di origine. Si utilizzerà invece uno script per generare i dati per caricare i record nella tabella.

Poiché i processi ETL necessari per il piccolo dello schema sono piuttosto semplici, la mappa di dati è bene come. In un progetto reale, È necessario annotare il mapping di dati da sottolineare quando sono necessarie trasformazioni complesse.

Creazione di data mart

Ora che modellazione logica è completa, È necessario creare le tabelle fisiche che caricherà i processi ETL e un database host per queste tabelle. Utilizzerò uno script T-SQL, di base per creare il database e la dimensione associato e tabelle dei fatti. È possibile trovare l'intero script T-SQL, nel download associato per la soluzione di BI di esempio in ( Download del codice 2009).

Ai fini di questo articolo, È necessario creare solo un sottoinsieme dell'intero schema mart dati di vendita in modo che può coprire l'intero processo ETL in SSIS. Nella versione più piccola dello schema, È possibile includere solo le misure OrderQuantity e ImportoVendite per la tabella dei fatti di vendita di Internet. Inoltre, il più piccolo dello schema include una versione semplificata delle tabelle delle dimensioni cliente, prodotto e la data.

Distribuzione di data mart

Per distribuire il data mart, È necessario semplicemente eseguire T-SQL ho scritto in precedenza per creare un'istanza le nuove tabelle in un'istanza di SQL Server. Eseguire T-SQL, È necessario avviare SQL Server Management Studio (SSMS) facendo clic su Start\All Programmi\Microsoft SQL Server 2008\SQL Server Management Studio. Quando SQL Server Management Studio Express viene aperto, È necessario digitare il nome del mio istanza di SQL Server designata e scegliere Connetti utilizzando l'autenticazione di Windows della finestra di dialogo connessione. Utilizzare SQL Server Management Studio per aprire il file TECHNET_AW2008SalesDataMart.sql ed eseguire lo script.

Lo sviluppo di processi ETL

Progettare e sviluppare processi ETL è necessario è il successivo passaggio nella creazione di una soluzione di BI. Per rivedere, ETL include tutti i processi tecnologici di cui dati vengono estratti da origini dati, trasformati e quindi caricato in un archivio di destinazione. In genere, processi ETL all'interno di soluzioni di Business Intelligence estrarre dati da file flat e database operativi OLTP, modificano i dati per un modello dimensionale (ad esempio, schema a stella) e quindi caricare i dati risultanti in un data mart.

Creazione di un progetto SSIS in BIDS

Il primo passaggio nello sviluppo di un processo ETL è creare un nuovo progetto in Business Intelligence Development Studio (BIDS). BIDS viene fornito con SQL Server 2008 e quando si seleziona l'opzione Componenti Workstation durante il processo di installazione vengono installati. BIDS include modelli di progetto per SSIS, SSAS e SSRS. Supporta inoltre integrazione del controllo del codice sorgente, come avviene in Visual Studio.

Per avviare BIDS, passare a SqlServer Start\Programs\Microsoft 2008\Business Intelligence Development Studio e selezionare progetto File\New. Verrà visualizzato il modello New Project nel Figure 3 .

Nella figura 3 nuovo modello di progetto in BIDS 2008

Selezionare il progetto di Integration Services nel riquadro Modelli, digitare ssis_TECHNET_AW2008 nella casella di testo nome e quindi fare clic su OK. BIDS ora dovrebbe essere visualizzato un progetto aperto di SSIS.

Creazione di connessioni dati comuni

Un'altra funzionalità interessante di SSIS 2008 è la possibilità di creare connessioni a origini dati all'esterno di singoli pacchetti. È possibile definire una sola volta una connessione all'origine dati e quindi fare riferimento in uno o più pacchetti SSIS all'interno della soluzione. Per ulteriori informazioni sulla creazione di origini dati BIDS, consultare" Procedura: definire un'origine dati mediante guidata origine dati (Analysis Services) ".

Creare due nuove connessioni di origine dati: uno per il database TECHNET_AW2008SalesDataMart e l'altro per il database OLTP AdventureWorks2008. Denominare le connessioni di origine dati AW_DM.ds e AW_OLTP.ds, rispettivamente.

Sviluppo ETL per dimensioni

ETL per caricare la dimensione Product è molto semplice. È necessario estrarre i dati dalla tabella Adventure Works Production.Product e caricare i dati nel database TECHNET_AW2008SalesDataMart. In primo luogo, È necessario rinominare il package predefinito che BIDS creati per il progetto SSIS. (Un pacchetto è un contenitore per tutti i passaggi del flusso di lavoro eseguirà SSIS). Clic con il pulsante destro del mouse sul package predefinito in Esplora soluzioni e selezionare Rinomina. Digitare DIM_PRODUCT.dtsx e quindi premere INVIO.

Successivamente, È necessario creare il pacchetto locale gestioni connessioni con le origini di dati predefiniti. Creare due nuovi gestioni connessioni fa riferimento a origini dati create in precedenza.

Definizione di estrazione e caricare un flusso di dati

SSIS include un'attività di flusso di dati che incapsula tutto ciò che è necessario per implementare l'ETL per una dimensione semplice. Sufficiente trascinare un'attività del flusso di dati dalla casella degli strumenti sulla superficie del flusso di controllo della finestra di progettazione e rinominare l'attività EL (per estrazione e carico). Fare clic con il pulsante destro del mouse attività flusso di dati nella finestra di progettazione e scegliere Modifica. BIDS viene visualizzata la finestra di progettazione flusso di dati.

La parte di estrarre il pacchetto di dimensione prodotto debba query nella tabella AdventureWorks2008 Production.Product. Per impostare questa operazione, È possibile trascinare un componente di origine OLE DB dalla casella degli strumenti sulla superficie della finestra di progettazione flusso di dati e rinominare il componente di origine OLE DB per AW_OLTP.

Successivamente, È necessario definire la parte di caricamento del pacchetto per caricare in data mart. È sufficiente trascina una nuova istanza del componente destinazione OLE DB nell'area di progettazione di flusso di dati e rinominarlo AW_OLTP. Quindi fare clic sul componente OLE DB origine (AW_OLTP) e trascinare la freccia verde viene visualizzato in origine OLE DB il componente destinazione OLE DB AW_DM per connettere i due componenti.

A questo punto, È stato aggiunto il flusso di dati i componenti necessari, ma è comunque necessario configurare ogni componente in modo che SSIS come si desidera estrarre e caricare i dati. Il componente destinazione OLE DB AW_DM fare clic con il pulsante destro del mouse e selezionare Modifica. Con l'editor OLE DB destinazione aperta, che assicurarsi che AW_DM è selezionato come il gestore di connessione OLE DB. Quindi espandere il nome della tabella di riepilogo e quindi selezionare la tabella di dbo.DimProduct. Infine scegliere la scheda mapping per verificare che i mapping siano corretti. Si sceglie OK per confermare i mapping. Questo processo è molto più semplice, in presenza di una mappa di dati pronta per riferimento, soprattutto se si lavora con tabelle di grandi dimensioni. Pacchetto ETL della dimensione prodotto è ora completata.

È possibile eseguire facilmente il pacchetto in BIDS. Per verificare il pacchetto di dimensioni di prodotto, aprire il pacchetto e premere F5.

Lo sviluppo di pacchetti aggiuntivi

Creare il pacchetto di dimensione Customer nello stesso modo che ho fatto il pacchetto del prodotto. Non ripetere i passaggi da effettuare per creare il nuovo pacchetto. È consigliabile per produrre autonomamente. Si noti che questo pacchetto utilizza una colonna di dati tipizzati XML (Person.Person.Demographics) nell'origine, che richiede di analizzare da singoli demografici relativi attributi. Per analizzare i singoli valori da una colonna di dati tipizzati XML di SQL Server, è possibile sfruttare un XQuery con metodo del tipo di dati XML nativo valore (). Nome del pacchetto completato DIM_CUSTOMER.dtsx.

Lo sviluppo di un pacchetto SSIS per la dimensione di data è facoltativo. Poiché la dimensione in genere non dispone di dati di origine, il modo più semplice per caricarla è in base utilizzando uno script T-SQL, di base. È possibile trovare lo script utilizzato nella soluzione completata.

Lo sviluppo il pacchetto di tabella dei fatti vendite Internet

Il pacchetto di tabella dei fatti vendite Internet esegue query per tutte le vendite di Internet e restituisce le vendite suddivise per prodotto, cliente e Data (ad esempio date di ordine). A differenza di un pacchetto di dimensione, un pacchetto della tabella dei fatti richiede un ulteriore passaggio per cercare i surrogati e i tasti smart nelle tabelle delle dimensioni corrispondente prior to il caricamento dei dati nella tabella dei fatti. È possibile creare un nuovo pacchetto e denominarlo FACT_INTERNET_SALES.dtsx.

La parte di estrarre il pacchetto deve interrogare il database OLTP AdventureWorks2008 utilizzando il codice T-SQL, illustrato nella Nella figura 4 .

Nella figura 4 T-SQL per codice vendite Internet per prodotto, data e clienti di

SELECT
       P.ProductID
       ,CONVERT(INT,CONVERT( CHAR(4), YEAR(H.OrderDate) ) + RIGHT('0'+ CONVERT(VARCHAR(3), 
            MONTH(H.OrderDate) ),2) + RIGHT('0'+ CONVERT( VARCHAR(3), 
DAY(H.OrderDate) ),2)) AS OrderDateKey
       ,C.AccountNumber 
       ,SUM(D.OrderQty) AS OrderQuantity
       ,SUM(D.LineTotal) AS SalesAmount
FROM
       [Sales].[SalesOrderDetail] D
INNER JOIN
       [Sales].[SalesOrderHeader] H ON (D.SalesOrderID = H.SalesOrderID)
INNER JOIN
       [Production].[Product] P ON (D.ProductID = P.ProductID)
INNER JOIN
       [Sales].[Customer] C ON (H.CustomerID = C.CustomerID)
WHERE
        H.OnlineOrderFlag = 1
GROUP BY
        P.ProductID
        ,H.OrderDate 
        ,C.AccountNumber

Consente di creare una nuova attività di flusso di dati in area flusso di controllo del pacchetto. Aprire la finestra di progettazione flusso di dati e creare un componente di origine OLE DB. Denominare il componente AW_OLTP e utilizzare la query nella Figura 4 come origine. La query restituisce un'aggregazione (somma) per le misure OrderQuantity e ImportoVendite trovate in tabelle di vendita di Adventure Works.

A questo punto è necessario configurare una trasformazione ricerca. Trascinare due nuove istanze del componente trasformazione Ricerca dalla casella degli strumenti all'area di progettazione flusso di dati e denominarli prodotti e clienti. Configurare il primo elemento (Product) per cercare il ProductKey nella tabella delle dimensioni prodotto eseguendo il join di AlternateKey della tabella delle dimensioni con il campo ProductID in ingresso dalla query origine AW_OLTP.

Configurare il secondo uno (Customer) per cercare il CustomerKey nella tabella delle dimensioni Customer unendo il AlternateKey della tabella delle dimensioni nel campo AccountNumber in ingresso dalla query origine AW_OLTP.

Passaggi finali

Il passaggio finale consiste nel caricare i dati nella tabella dei fatti FactInternetSales, sostituendo le chiavi naturale per ogni dimensione con le chiavi surrogate trovata la trasformazione ricerca. Trascinare una nuova istanza del componente destinazione OLE DB e il nome AW_DM. Modificare il componente destinazione OLE DB e selezionare il gestore di connessione AW_DM. Selezionare la tabella dbo.FactInternetSales e fare clic sulla scheda mapping. Assicurarsi che i mapping aspetto simili a quelli nella Figura 5 . Fare clic su OK per completare la logica del pacchetto.

Nella figura 5 mapping della destinazione di OLE DB per la tabella SalesFact di fatto Internet

Per verificare il pacchetto Internet dei fatti di vendita, aprire il pacchetto in BIDS e premere F5.

Acquisite ora le nozioni di base di modellazione dimensionale e la creazione di pacchetti ETL progettata con SSIS. Nel terzo articolo questa serie, verrà illustrato come utilizzare un popolato data mart per creare dimensioni e cubi per un database SSAS. Dopo avere generato un cubo, è quindi possibile sviluppare un pacchetto SSIS per aggiornare questi oggetti continuamente nel database SSAS che viene aggiunti ogni volta di nuovi dati per il data mart. SSIS anche preparare i dati da visualizzare in un report SSRS quando i requisiti di report non possono essere soddisfatte con una singola query. Come si può vedere, SSIS possibile eseguire molto più consente di gestire la soluzione di Business Intelligence più semplicemente elaborazione ETL.

Derek Comingore è un progettista di senior con ComFrame. MVP per SQL Server e un certificato professionale numerose tecnologie di Microsoft, tra cui Business Intelligence di SQL Server partecipa spesso a gruppi utente di SQL Server locali e nazionali ed è un autore pubblicato sugli argomenti di SQL Server. Derek è incentrata sulla creazione e a livello aziendale di data warehousing e soluzioni di Business Intelligence con SQL Server insieme con gli altri prodotti di software Microsoft BI. È possibile contattare Derek all'indirizzo dcomingore@comframe.com.