We are currently planning the data cubes for a SQL Server 2005 analysis
project. Users will view the date using ProClarity (not sure if that's
important).

The data that we want loaded into the data cube is our clients' sales data
(we currently have 4 clients). The data will always be viewed from a client's
point-of-view, so a user from Client1 should be able to see all Client1's
data and none of the other clients' data.

Currently, we are planning to do this by creating a cube for each client.
Although the datawarehouse database will have all our clients data, each cube
will only be loaded with that client's data. And users from a client will
only have access to their cube. Although this does what we want, it does mean
creating an identical cube for each client.

Does this seem like the best solution ? Are there any other ways to achieve
the same thing ?

Does anyone have a similar situation ? And how have you dealt with it ?

Thanks,
Craig

A 'clients' dimension could be set up, and then a role set up for each client
- and 'dimension data' settings for each client could then limit the client
to their particular member of the clients dimension. Whilst not something
I've previously had to do, it might be an avenue worth investigating. If
it'd be easy enough to use such a dimension to select the right data within
the cube, I'd go with this (subject to caveat below).

A separate cube for each client is a good option if you're going to be
dealing in a great deal of data for each client - depending on how to
anticipate performing updates to dimensions/measure groups. You don't want
to end up with a monolithic cube that takes days to process, one assumes!

"Craig HB" wrote:

Craig -

I think it would probably be best to go with separate cubes, unless you also
have a business need at times to see all the data consolidated in a single
cube. If you had that, it would maybe be best to use a single cube with a
client dimension and only give each client access to their slice of that
data.

We have set up systems to do that by using local cubes and our local cube
generator (previously called the Local Cube Task, now called CubeSlice). We
have an illustration of how to do that on our web site using public campaign
data available from the Federal Election Commission. We have three server
cubes and then create separate local cubes slicing by state, so that we can
send out a package of data with FEC data, one for each state.

Slicing the cubes makes them small enough to distribute and the smaller
cubes also have much better performance.

We are making all these cubes available as a public service in a set of
presentations leading up to the 2006 Congressional elections. We're updating
the data every day, with the current data files available from the FEC.

I think it's a good illustration of what you can do with slicing a single
server cube into many cubes for individual users. And I think it's a great
way to view campaign finance data.

Tim Peterson
www.cubeslice.com


"Craig HB" <CraigHB (AT) discussions (DOT) microsoft.com> wrote