How to create a schema with unrelated client dimensions

Hi,
Currently I am using a standard star schema for each client. The reason for having an individual star schema by client is that for each client we allow client specific data points (dimensions). Now that our list of clients is growing, this approach is not sustainable.

So basically the data set consists of data that we generate and data that gets passed on by the client.

The main problem lies in the fact that we allow each client to pass on any number of data points. The data points are normally demographic, geographic, behavioural data. There are some data points that might be the same among clients, but most of them are not.

We have our own data points, which mostly stay the same, only every 2 or 3 months we might add a new one.

So what I am trying to find is one model (and only one) which can hold all client data. I haven't come across a model yet that offers such a flexibility, hence I am asking on this forum.
Another point to keep in mind is that the model should be able to handle vast amounts of data.

I'd appreciate if somebody could provide some feedback on this.

Hi ds,

A nice challenge! I'm imagining a "Data Points" dimension with attributes of:
Source (e.g. you or a client ref ID)
Data Point Name
Category
Value Format (e.g. Percent, Currency, Number)
SCD type 2 attributes e.g. Start Date, End Date, Current Flag
and anything else that's common to all Data Points

Then your "Data Points" fact probably only needs
Data Point SK
Date
Value

That's looking quite lean, so it should be able to scale a long way.

Presentation will be a challenge, the tool needs to be able to separate the Data Points (i.e. security) and probably vary the value formatting. I'd suggest SSAS where security is flexible and you can alter measure formatting based on dimension properties.

Good luck!
Mike

Hi Mike,
Many thanks for your reply! Initially I was thinking of something similar, but the problem arises with the internal data points that I have (which are fairly stable). I.E.:
date (dim)
ad_name (dim)
browser_type (dim)
operating_system (dim)
... some 20 more dimensions ...
# impressions (measure)
# clicks (measure)

In addition to this I have to include the variable amount of client data points (this will be all dimensions).

So I could have one fact table which holds the internal data with one foreign key pointing to a bridge table and a foreign key from the bridge table to the table that holds the client data (i.e. Key, data point name, value). Now, I don't think this would scale very well because it results in a rather complex join and some additional operations.

Overall it's a rather complex scenario, so any advice is really appreciated!
Best regards,
DS