Overview
In this chapter we will look at the steps to setup a test environment using the AdventureWorks databases.
Explanation
Follow the steps below to create an environment where we can learn more about DAX.
Setup a tabular SSAS database
A detailed discussion of Data Modeling in Tabular SSAS is out of scope of this tutorial, but we need some tabular structure where we can work with DAX. Follow the steps below so we have a database to work with.
Step 1: Download “Adventure Works DW 2014 Full Database Backup” and “Adventure Works Tabular Model SQL 2014 Backup” files.
Step 2: Assuming that SQL Server and SSAS Tabular is already installed, restore the DW backup file on a SQL Server instance, and Tabular model backup on SSAS instance.
Step 3: Open a new SSAS Tabular project in SQL Server Data Tools by using the “Import from server (Tabular)” template and selecting the tabular model that we restored in Step 2.
Step 4: Once you create the project, open the Model.bim file. It won’t have any data yet. From the Model menu, select Process > Process All option. This option will read data from the DW and process all the tables in the model. Post processing you should be able to see data in all the tables.
Step 5: From the model menu, select Model View > Diagram View. You should be able to see a model as shown below. Now we have a database ready to practice DAX.
Additional Information
- Consider exploring the data model to understand the different tables, relationships and the type of data in different 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
