We have this Data Backup property on the .bim file that by default is turned off. You may wonder why this property exists. Let us explore what it is this property does, and why it (and backups in general) are useful in the tabular designer.

The Data Backup property specifies whether or not an ABF file is created whenever the .bim file is saved. When you save, we create an ABF backup of the workspace database, so all the data you have processed into the workspace database is preserved. This file is saved in the same directory as the tabular project.

There are three scenarios in which taking a backup from directly from the designer is useful. There’s a fourth scenario in which the ability to restore from a backup taken from outside the designer is useful. Let’s walk through them.

Scenario 1: Multiple developers

Alice, a developer, builds a very nice model and wants to share it with Bob, another developer. Bob wants to see the data that Alice saw when she was modeling, but Bob doesn’t want to sit around all day processing his workspace database when he receives the .bim file and the .smproj file. What to do?

Here is how Alice and Bob can share the data:

  1. Alice changes her Data Backup property to “Back up to disk”.
  2. Alice provides her .smproj, .bim, and .abf files to Bob.
  3. Bob places the .abf and .bim files in the same directory, without changing their names.
  4. When Bob opens the .bim file, the .abf file is restored to his newly created workspace database. He can now model against metadata and data.
Scenario 2: Close without save

As I mentioned in my tabular model architecture post, every change you make in the tabular designer sends an alter to the workspace database. If you save your changes when you close the designer, the workspace database and the .bim file stay in sync on close. The next time we open the .bim file, we attach the workspace database, everything is as you left it, and you are good to go.

However, when you close without saving your changes, the metadata in the .bim file may become out of sync with the workspace database. We always assume that when you open the .bim file, you want to see the metadata from the .bim file, not the metadata from the changes you abandoned previously.

If you don’t have a backup of your model, you wind up with some potentially funky behaviours when you close without save. For example, say you delete a table, close without saving, and re-open your model. The metadata for the table re-appears, as expected. We apply the changes from the .bim file to the workspace database. However, the data from the table is gone! This is because you deleted the data from the workspace database when you deleted the table originally.

To avoid getting yourself into this partially processed situation, set the Data Backup property to “Back up to disk”. That way, you always have a set of data that matches the metadata of the .bim file. We can then restore this ABF on load as necessary to ensure you are always modeling against a full set of data.

Scenario 3: Workspace databases are deleted on close

By default, we detach workspace databases on close. If you work with lots of tabular projects, your OLAP\Data directory for the workspace database server gets clogged with all the temporary workspace databases. One way to avoid this problem is to change the Workspace Retention property of the .bim file to “Delete workspace”. When you close the .bim file, the workspace database gets deleted, and your OLAP\Data directory remains fresh and clean.

The problem with that approach, of course, is that when you reopen the .bim file, the data is gone. This problem can be avoided by setting Data Backup property to “Back up to disk” when the Workspace Retention property is set to “Delete workspace”. On next open of the .bim file, we restore the .abf file on open, and your data re-appears.

Scenario 4: Quick changes when the original .bim file is not available

Alice gets a frantic phone call. Her customer, Eve, needs a small change to her model (say adding a measure) immediately! Alice wants to see the impact of her changes as she works, so she wants to model against metadata and data. Unfortunately, Alice doesn’t have the original sources for Eve’s model, so she doesn’t have any data hanging around locally. Online mode is not supported for tabular models. What to do?

Alice knows that she can use Import from Server (Tabular) to create a model that matches the metadata of Eve’s deployed model. However, Import from Server doesn’t bring the data. There is a hack that Alice can do so that she can see the data locally without waiting all day for the data to process (assuming Alice even had permission to Eve’s data sources, she probably doesn’t). Here’s how:

  1. Alice goes to SSMS, takes a backup of Eve’s production database. She saves this as Model.abf.
  2. Alice creates a new project using the Import from Server (Tabular) template. She imports from Eve’s production database. She saves and closes her solution without renaming the Model.bim file.
  3. Alice makes sure Windows Explorer is configured to show hidden files and folders. She navigates to the directory for the project she created in step 2. She takes the following actions:
    • She copies the Model.abf file created in step 1 to the project directory
    • She deletes the hidden Model.bim_alice.settings file.
  4. Alice goes back to BIDS and re-opens her project from step 2. The ABF is restored from the backup directory, and now Alice can model against metadata and data.
Important notes about taking backups

Here are some important things to keep in mind if you want to take backups from the designer:

  • Taking a backup on save increases the save time. If you have a large model or large data set, this increased save time may prove too much for you.
  • The service account of the workspace database server must have sufficient privileges to access the file location where the tabular project is saved. So, if your service account is a low privileged user (eg the default NT Service\MSSQLServerOLAPService account) and you try to take a backup to the default location of tabular projects (under Documents\Visual Studio 2010\Projects),  the service account fails to access the file location of the ABF and gives a cryptic error.

A solution to this problem is to use a higher privileged user (such as yourself) as the service account for the workspace database service instance. This is not secure. It violates the principle of giving least possible privilege to service account users. That said, it is a very practical solution for many people.

  • You cannot take backups if you are modeling on a remote database server. Because it is unlikely that the service account user on the remote server will have access to the likely location of your tabular project (your local machine), we simply block taking backups at all times when modeling on a remote workspace database server.

The text for those last two bullet points may look familiar to some people. I copied and pasted them from the cryptic errors when importing from PowerPivot post and then subbed “PowerPivot workbook” for “Tabular project”. The fundamentals of the experience are the same for both, because both PowerPivot workbooks and ABF backups begin from the same action (restoring from image). Now you can really start seeing the impact of the out-of-proc server architecture.

PS – I just introduced you to the extra files I alluded to in the tabular project structure post, albeit in a roundabout way. Those two files are the ABF file and the hidden user settings file. You will hear more about the hidden user settings file later.


Blog Post by: Cathy Dumas - MSFT