2

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

2
  • 1
    By "multidimensional data warehousing" do you mean a multi-tenant data warehouse? Feb 13, 2012 at 17:59
  • Hi Nick. That looks pretty interesting. I'll have a look at it :) Thanks
    – MicBehrens
    Feb 13, 2012 at 18:36

2 Answers 2

4

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.

3
  • I wrote about EAV here : sqlblog.org/2009/11/19/what-is-so-bad-about-eav-anyway - this is not an answer obviously but my opinion and the follow-up commentary might be interesting to the OP. Feb 13, 2012 at 18:06
  • I've been looking into msdn.microsoft.com/en-us/library/aa479086.aspx which Nick posted as a comment to the question. Here is 3 scenarios, where the third is pretty interesting. This scenario is using shared database, schema and tables, but separated by TenantIDs indexed by Views. Would this be "just as good" as the scenarios you guys are mentioning?
    – MicBehrens
    Feb 14, 2012 at 11:12
  • The article addresses slightly different issues to what I am talking about - The posting discusses various options for customising the database schema on a per-customer basis. You could apply the customisation approaches to any of the tenanting strategies described in the article. Feb 14, 2012 at 12:08
0

If I understand you correctly, I would just put all of the data for all clients into the same table, and add a client id column. If the tables are of significant size, then partition the table based on the client id.

This assumes that you intend to keep all clients on the same schema at all times of course.

2
  • 1
    Would anyone else recommend going down this path?
    – MicBehrens
    Feb 14, 2012 at 0:07
  • @Behrens it has been a while, but I would be interested to hear in the outcome. I face almost the same exact scenario.
    – amaster
    Oct 29, 2019 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.