When a Microsoft Analysis environment has several servers with different editions, you always have to weigh how you keep the servers in sync with each other.

In the specific case of SSAS, the standard edition lacks functionality such as semi additive measures, which are available in enterprise. As soon as you want to link 2 servers (std and ent), a number of scenarios are ruled out, such as linked objects, synchronization and the use of the deployment wizard.

If we look specifically at the semi additive measures, you certainly want to use them. In short, on this point you do not want to compromise by not using them.

So which options are left to deploy a cube on another machine (std edition) as well:

  • Rip the semi additive measures out. From then on you have several versions to maintain;
  • Make a backup and restore it on the standard server.

The interesting part of this last option is that reports can access the cube, even when it has semi additive measures. But this gives another interesting challenge, because we cannot process the cube with new data - semi additive error message. This means that every time you need new data on the standard server, you must run a backup/restore procedure by hand, and that is of course a bad option. There are no SSIS tasks for this. There are for SQL Databases, but not for SSAS databases.

The following solution is available, and it means you do not have to upgrade to the enterprise edition: create XMLA scripts for all the different parts of the cube (database, data source, dsv, dimensions), and execute them in an SSIS package with the “Analysis Services Execute DDL” task. Then load new data into the dimensions and cube with the “Analysis Services Processing” task. Make a backup with the same DDL task, and load it on the other server.

It takes a while to generate these XMLA scripts, and you still need a maintenance procedure when the structure of the dimensions or the cube changes. But that is of course much less work than running a procedure every day.

Frequently asked questions

Which functionality do you miss in SSAS Standard compared to Enterprise?

Among other things, semi additive measures. They are only available in Enterprise. If your cube uses them, you cannot simply move it to a Standard server.

Which scenarios are ruled out when you link an SSAS Standard and an Enterprise server?

Linked objects, synchronization and the use of the deployment wizard do not work between a Standard and an Enterprise server.

Can I restore an SSAS cube with semi additive measures on a Standard server?

Yes, with backup and restore. Reports can then access the cube. But you cannot process it with new data, because that gives a semi additive error message.

How do I deploy an SSAS cube to a Standard server without upgrading to Enterprise?

Create XMLA scripts for the database, data source, dsv, dimensions and cube. Run them with the Analysis Services Execute DDL task in an SSIS package. Load the data with the Analysis Services Processing task.

Is there an SSIS task for backup and restore of an SSAS database?

No. There are SSIS tasks for SQL databases, but not for SSAS databases. With the Analysis Services Execute DDL task you can make a backup and load it.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. BIDS Helper

    Data Warehousing16 Feb 2009 · 3.9k reads
    3.9k

  2. Custom Logging in SSIS

    Data Warehousing17 Feb 2009 · 3.8k reads
    3.8k

  3. Analysis Services, Reporting and Excel 2007

    Data Warehousing5 Feb 2009 · 4k reads
    4k
(3 rows affected)Data Warehousing