Overview
AdventureWorks is the sample database available from Microsoft for different purposes as well as different SQL Server versions. We need to use the AdventureWorks DW 2008 R2 database for our cube design and development. This database contains dimension and fact tables with prepopulated data. We can use this database as a launchpad to start our SSAS project. Developing a data mart is out of the scope of this tutorial, so we will use this sample database.
Explanation
To install the AdventureWorks database, navigate to the codeplex (http://msftdbprodsamples.codeplex.com/) site and download the MSI for the version of SQL Server you are using. This tutorial expects that the reader is using SQL Server 2008 R2, and all the exercises will be using this version of SQL Server.
After downloading, start the installer and you should get a screen similar to the one below.
AdventureWorks Data Warehouse 2008R2 is the database we need for our exercises. Point the installer to the SQL Server instance that you are using, and install the database. After the database in installed, open SQL Server Management Studio to verify the databases that were installed. You should find something similar to the below screenshot.
Expand the database higlighted above and check out the different Dim and Fact tables in this database. The tables having the prefix Dim are suited to be used as Dimension tables, and tables having prefix Fact are suited to be used as Fact tables.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019