1 / 53

DATA WAREHOUSING IN SQL SERVER 2005/2008 BUSINESS INTELLIGENCE

Pertemuan VIII. DATA WAREHOUSING IN SQL SERVER 2005/2008 BUSINESS INTELLIGENCE. Experience with BI. Developing end-to-end BI prototype for Plan International Extensive project experience with Reporting Services Books, courses, exams and played

delano
Download Presentation

DATA WAREHOUSING IN SQL SERVER 2005/2008 BUSINESS INTELLIGENCE

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. Pertemuan VIII DATA WAREHOUSING IN SQL SERVER 2005/2008BUSINESS INTELLIGENCE

  2. Experience with BI • Developing end-to-end BI prototype for Plan International • Extensive project experience with Reporting Services • Books, courses, exams and played • Developed numerous reporting apps that should have been BI solutions!

  3. Core Processes • ELT (Extract, Load, Transform) • Import, clean and combine diverse data • Store in Data Warehouse • Star Schema • Relational structure for OLAP, not OLTP • Cubes • Pre-aggregate results (sum, count) • Reporting • (Data Mining and Prediction)

  4. SQL Server 2005 • SQL Server Management Studio • Design and query databases • Replaces Enterprise Manager, Query Analyser • Manage Servers (DB, AS, RS, IS) • SQL Server Business Intelligence Studio • VS .NET 2005 projects • Design, build and deploy

  5. Business Intelligence Studio • Integration Services • Data import and transform (replaces DTS) • Merge data into Star Schema • Analysis Services • Design and populate Cubes • Report Model • ERD for reporting • Report Server • Design and publish reports

  6. Star Schema • Star focuses on business area • Sales, Logistics • Fact Table stores numeric measures • Order Value, Order Quantity • Dimension Tables • How data is analysed (aggregates and heirarchies) • Product, Customer, Time

  7. BI IN SQL SERVER 2005 • Database Service adalahlayanan-layananumumuntuk basis data yang paling dasar. • Analysis Service adalahfitur-fituruntukdatawarehousing, OLAP dan Data Mining.

  8. Database AdventureWorks Microsoft Corp menyediakan sample database bernamaadventureWorks yang beradadibawahshemadbodan data yang bukanberadadalam schema dbo.

  9. Database AdventureWorksDWadalah database fiktif yang disertakan Microsoft Corp tentangsebuahperusahaan yang memiliki data yang sangatbesardanlengkap

  10. Link download: http://msftdbprodsamples.codeplex.com/releases/view/4004 Pilih data yang akandiambil, misal: AdventureWorksBI.msidan AdventureWorksDB.msi KemudianInstall dansecaraotomatis, lokasihasilinstalasi sample database tersebutadalah: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ BukaSQL Server Management Studio danambil (attach) database tersebut.

  11. Dua data tersebutterdiriatasberbagaitabel yang salingberelasi, untuk database AdventureWorksDWsudahterdiriatasbanyaktabelfaktadandimensi-dimensinya yang berbentuk: • Star Schema • Snowflake Schema • Constellation Schema

  12. Dari database tersebutjugadapatdibuatbeberapakubusmenggunakan SQL Server Business Intelligence Development Studio, misalnyadaritabelFakta Internet Sales.

  13. Contoh query SELECT FirstName, LastName, City FROM DimCustomer C, DimGeography G WHERE C.GeographyKey = G.GeographyKey;

  14. Business Inteligence

  15. To create a new data source view, follow these steps: • Right-click on the Data Source Views folder in Solution Explorer and select New Data Source View.

  16. BIDS will automatically display the schema of the new data source view

  17. To create the new cube, follow these steps: • Right-click on the Cubes folder in Solution Explorer and select New Cube.

  18. Deploying ,Processing, Browsing a Cube

  19. Terminologies • Cube The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that’s been aggregated to allow queries to return data quickly. • Dimension Each cube has one or more dimensions, each based on one or more dimension tables. A dimension represents a category for analyzing business data • Fact table A fact table contains the basic information that you wish to summarize. This might be order detail information, payroll records, or anything else that’s amenable to summing and averaging.

  20. UNCC MSDNAA: http://msdn04.e-academy.com/elms/Storefront/Home.aspx?campus=uncc_cit SQL Server 2005 Standard Edition SQL Server 2008 Developer Edition SQL Server 2008 Enterprise Edition WHERE TO GET SQL SERVER

  21. Deploy pada Visual Studio dan SQL Server

  22. Proyek-proyek SQL server yang dibuatdenganmemakai Visual Studio secara default hanyaakanmenghasilkan intermediate code, belummengalokasikan user level cubes secarafisik.

  23. Deployment berefekpadaduahal: • Menghasilkan executable-code file • Mengalokasikan user-level cubes di database-database Makaartinyauntukmendapatkananalisislebihjauhdarisuatu database kekubus (misal data mining), makaterlebihdahuluproyekharusmengalami deployment

  24. Langkah Deploy Proyek:

  25. atau…

  26. Jikatidakterjadimasalah (instalasilengkap) makaakanmuncultampilandisudutkananbawahsepertiini:

  27. Setelahproses deploy selesai, barulah tab-tab diatasdapatberfungsisepenuhnya Isidarikubus

  28. MenampilkanIsi Data Kubus

  29. Drag data ke browser

  30. Drag data yang lain

More Related