Multidimensional databases on SQL Server 2008 R2? I am developing a business intelligence-product using ASP and SQL Server 2008 R2.

Each time I get a new client I'm copying a current client's database without data. This is OK for now, but can be annoying on the long term.

My new problem is: When doing further development on the product with changes on the database setup (column names and such), then these changes needs to be done on every client-database.

Is it possible to do any kind of multidimensional data warehousing - Having separate data-entries on the "same" table?

I hope you know what I mean :)

FYI: Each client have its own user account mapped to their own database to increase the security
On the trials and tribulations of making generic B.I. products

For the moment I'm assuming that the physical structure of the data model remains the same and you just want to change fields and their definitions to site-specific customisations.  If you want to frig the database structure you're really into building bespoke systems.  The other assumption that I'm going to make is that you have a star schema.

This can be done with metadata for each of the dimensions and fact tables that has the attributes for each table.  This metadata allows you to construct a customised view over a generic base structure.  You will have to structure the ETL to map to the base data but you can configure a customised view over it.

There are a couple of ways you can manifest the base tables: generic columns and an entity-attribute-value structure.  In each case, the customer's view is customised by metadata stored in the system.

Generic columns

A table with generic columns will have columns with names like 'Code1', 'Code2' etc. and groups of columns of particular types (code, money, float, int etc.).  You implement a table like this for every dimension in your system, and a table with some integer and money values for each of the fact tables. 

This is customised by creating a view that links the columns to a business facing name.  Te metadata is used to generate the view.  If you have cubes and/or a suite of reports sitting on top of this, you can also programatically annotate the dimensions and report fields based on the metadata, allowing them to be productised but customisable.

The view is simply a view over the base table that renames the columns.


Advantage: The structure is relatively efficient to query, even through the views.
Disadvantage: The tables have a finite number of columns, so you may need to alter them if you run out.
Disadvantage: The meaning of the underlying data is quite opaque without the view, which doesn't help the readibility of the ETL code.


Entity-Attribute-Value

An entity-attribute-value structure has a 1:M relationship against the base table.  The relationship has the attribute type (from a reference table) and the value against the parent entity's primary key.  

You have a base table, a reference table for the attribute names and types and a link table that records the value for each attribute against the entity.  It is commonly used on software products.  The view has to calculate a pivot or similar function to flatten the data out and can be generated from the attribute list for the entity type.


Advantage: Infinitely flexible - you can add attribubtes by updating a reference table, although you will need to modify the ETL code to populate it.
Disadvantage: Relatively inefficient and fiddly to query directly.  You may want to build a supplementary process to produce a flattened view.  The code that does this can be generated from the metadata, though.


Similar things can be done with base metrics on the fact tables.

A bit of experimentation will show you how to programatically annotate columns onto a cube dimension or report model, so it is possible to make a template and use a metadata based system to synchronise the cubes or front-end artifacts by generating columns.

If you build reports in a templatable way (base columns with particular signatures) you may also be able to do something similar with canned reports if you have a significant body of these as a part of your product.  This might be getting into diminishing returns, though.

One golden rule:

Never, ever edit generated code, or make an architecture that relies on subsequent human intervention in generated code.  That way lies madness.