CODEWITHSUNDEEP
sql-server-2016ssas-tabular
hkravitz
hkravitz

Reputation: 1385

Creating SSAS Tabular Partitions in SSMS is not reflected in SSDT solution

I'm using SSDT to create my Tabular model, I'm creating a table that I'm partitioning (Two weeks of data - 24 Partitions per year) See below.

Partitions

Usually I'm preparing 2 years of data partitioned (meaning 48 partitions). When I'm deploying the model to Analysis Services I can access it from SSMS by connecting to my Analysis Services instance

Partitions2

My question is,

I've managed to create an automated script that generates the XMLA query of creating the partitions in SSMS, I'm executing it and I can see the partitions being created, However when returning to SSDT and opening the solution these partitions are not reflected there. is there a way to "force" SSDT to read the meta data from the analysis services instance upon opening the solution again?

Additionally, If I continue developing the model in SSDT, once I'll deploy it again all the changes I made via SSMS will be overridden, is there a way to avoid that?

Creating partitions manually in SSDT can be very painful... I've managed to create a script that will automate it, but not in SSDT

Any suggestions?

Upvotes: 0

Views: 1466

Answers (2)

Dan
Dan

Reputation: 10680

As userfl89 already pointed out, any partitions that you create in SSMS need to be "backported" into your SSDT project, for example by using the "Import From Server (Tabular)" option when creating a new project. Otherwise, you risk losing the partitions (and the data contained in them) when deploying from SSDT.

Alternatively, you can use BISM Normalizer - a plugin for Visual Studio - to merge changes (such as partitions) back and forth between SSDT and the deployed database.

There's also the Analysis Services Deployment Wizard which takes the contents of your projects \bin\ folder and lets you deploy to a database, specifying that you don't want to overwrite existing partitions.

Lastly, if you haven't already, I would recommend taking a look at Tabular Editor. It's an alternative to SSDT for developing the model, so there will be some learning involved of course, but the good news is that you can do partial deployments, in order to avoid affecting the partitions on the already deployed database.

enter image description here

Upvotes: 1

userfl89
userfl89

Reputation: 4800

The database that you're accessing in SSDT is your workspace database. The workspace database is essentially a local copy of the tabular model. The partitions you added to the model in SSMS were created, the workspace database is just out of sync. Your can overwrite your workspace database with the current version of the model by deleting/moving the files used in your local SSAS project, then creating a new Analysis Services project in SSDT and using the "Import From Server (Tabular)" option, then selecting the current version of the tabular model. This will create a new workspace database using the current version of the model. When doing this, make sure that when you delete or move the files from your local SSAS project, the files you move are for your local project, not the actual model. If you need to verify the location of the files used by the model, the DataDir property of the SSAS instance in SSMS will show this file path.

Upvotes: 1

Related Questions