How to Build a Tabular Model in SSAS

Edwin Davis Sep 06, 2019

In the release of SQL Server 2012, Microsoft introduced the SQL Server Analysis Services (SSAS) tabular model, a database that runs in-memory or in Direct Query mode. These databases display data with relational data sources. Allowing them to deliver a quick and powerful way of providing self-service Business Intelligence to client applications such as Microsoft Power View and Microsoft Excel.

Knowing how to build these databases or Tabular models is one thing but knowing how to build a Tabular model the right way is something completely different. Today, I want to demonstrate 5 steps on how to build a Tabular model in SSAS and how to deploy to a local instance.

So, let’s get started

Step 1: Create a new project in Visual Studio. Select Tabular, hit next and give the project a name. I suggest testing your connectivity before going on to step two.

Step 2: Import your data into Visual Studio and connect to SQL Server or a local instance. I connected to Adventure Works. Test your connection then use our Windows Server login and choose a couple of tables. These tables will confirm that we have data and a relationship to work with.

Step 3: Create a measure and a short DAX expression. Check the mark to confirm the measure is valid. Be sure to save often during this process.

Step 4: In Solution Explorer right-click on the solution and bring up properties. In these properties confirm the location of where you want to deploy. In my demo I am deploying to a local Analysis Services Server but you can also deploy to an Azure Analysis Server Instance by placing the name of that in the Server field.

In this case, I change my processing settings from default; if you’re like me and want to control when your processing happens, change the processing settings to Do Not Process and click OK.

Step 5: Click on Build Solution. Hit deploy and this will allow you to deploy to your target environment. Please note it may take a few seconds to deploy to your target destination and don’t forget to confirm after your solutions have been deployed.

I hope these 5 steps have helped you in creating a Tabular model. If you’re new to Tabular modeling or wanting to learn best practices around this or any Azure product or service, please click the link below. We would love to help you take your business from good to great! Thank you for reading and we hope to see you in the next blog.