Create Project from a Deployed SSAS Tabular Database

Problem

You need to create project files for a SQL Server Analysis Services (SSAS) tabular database deployed on a SSAS Tabular Instance, but you don’t have access to the project source for some reason. Creating an entire project from scratch that reflects the deployed database is not an option, so in this tip we will learn how to handle this situation.

Solution

We will walk through the steps to create project files for a SSAS database deployed on a SSAS Tabular instance.

For the purpose of this demonstration, I have installed the AdventureWorks Tabular 2014 Database on my machine. Follow the below steps to create a project from this database using SSDT.

Step 1: Open the SQL Server Data Tools (SSDT), and select the menu option “File” > “New” > “Project”. Select Import from Server (Tabular) option as shown below and click OK.

Step 2: The next step is to select the Analysis Server that will be used by the new project. You can type the name of the server or in the case where you have SSAS installed locally, you can type localhost as shown below.

Step 3: The next step is to select the Analysis Server on which the database is hosted, and the database name which you intend to import the project structure. Select the same as shown below and click OK.

Step 4: The above step will create the structure, but it will not have any data. In order to retrieve data into the model we need to process the structure. To ensure that we have connectivity to the source from which the SSAS database is sourcing data, click on the menu option “Model” > “Existing Connections”. Make sure you are able to connect to the data source of the SSAS database as well as the Impersonation Settings are configured with credentials that have sufficient privileges to process the data.

&nbsp

Step 5: In order to process the data, select the menu option “Model” > “Process” > “Process All”. This will bring up a dialog box that will show the processing progress. Once the processing is complete, you should be able to see results similar to below.

Step 6: Post processing when you open the model in data view, you should be able to find data in the designer.

In this way one can create an entire new project from a deployed SSAS Tabular database.

Next Steps

• Try to ensure that the project deployment configuration settings match the deployed database, in order to modify the database during deployment instead of creating a new database.
• Read more Analysis Services Tips

Siddharth Mehta

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