1 database, 18 cutomers - how many cubes?

Hi all, i'm after advice rather than a technical answer, therefore the best advice gets the points - (a reason why your advice is good would be very helpful).

I have 1 database with customer data in it.
I have 18 customers who each want to use BI to analyse their data.

Should I create one cube and use logins and roles to split the data between customers or should I create 1 cube for each customer?

My concerns are mainly with speed, recovery and security - starting with the following:
Would it be more efficient to build lots of small cubes or 1 big one?
If I build 1 cube & it fails it takes ages to reprocess.  It also takes ages to restore from a backup.
If I build lots of cubes, every time a new customer is added, it opens up a massive security risk window having to go through all the tick boxes and make sure they're ticked.  Would changing the fact table critieria when building the cube for each customer be a safer option (albeit still a security risk if someone does it wrong).
Currently the SSIS package the builds the 1 big cube fails about 30% of the time, although the package has been processing the cube in one go.   Tonights will process each partition of the cube, if that doesnt work i'll break it down to data & index level, but thats diverting from this question a bit!

Any help/tips/advice would be much appreciated.

Who is Participating?
PFrogConnect With a Mentor Commented:
I would use a single large cube, and use roles to filter the data down. This will significantly reduce the time required to manage the system, as well as the potential security that you already mentioned. The other huge benefit is that a single cube will allow you to report on all of your customers' data at once if required.

However, I would be more concerned with why your cube is failing 30% of the time... Is this because of errors in your source data? If so I would put some time and effort into cleaning the data before the cube is processed. I would have thought a simple check on missing foreign keys should solve most of the problems. Move these facts to a temporary table so that these can be corrected manually before being re-imported into your main fact table.
Do actually answer your question on performance, your would have to run a test to be sure, but I would expect a single cube to process faster, as I presume that a lot of your fact records (across multiple customers) are going to share a lot of common dimension records. Thus you would be significantly shrinking the total number of aggregations being stored.
kintonAuthor Commented:
Thanks for your comments PFrog.
It failed again last night.  It fails with:
[Analysis Services Execute DDL Task] Error: Memory error: Allocation failure : Not enough storage is available to process this command.

[Analysis Services Execute DDL Task] Error: Errors in the OLAP storage engine: An error occurred while processing the 'Factrenewals Other' partition of the 'Renewals' measure group for the 'Renewals Cube' cube from the MASS database.

I have been looking into this.  First I found this hotfix:
http://support.microsoft.com/kb/917885 (our version of msmdpump.dll is 9.0.1399.0)

I ran an @@version and got this:
Microsoft SQL Server 2005 - 9.00.3033.00 (Intel X86)   Dec  6 2006 17:53:12   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I thought that was SP2, but it seems there might be a 2 versions of SP2.  I read a few posts about 9.00.3033.00 being a test one and 9.00.3042.00 being the proper release.  

However, I have also seen reports that previously working SSIS packages stopped working when 9.00.3042.00 was installed.

My line manager wants me to be sure that the update will resolve our issues and not cause new ones, but I can't seem to find information to prove either.  Any tips?
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Do you have a test server that you could install SP2 on?
Unfortunately there is no way (that I am aware of) to uninstall SP2, apart from a re-install of SQL. Therefore running a test on your live box may not be possible.

The proper version of SP2 is 3042, and then there are a few subsequent hitfixes. I am running 3054 at the moment.

There's a couple of things I'd do to try and solve this problem, before installing SP2

1) Create a number of different processing tasks, each processing one or a few dimensions (if a few, set them to process sequentially, and in seperate transactions), and then the final one processing the fact table (or one per fact table if you have multiples). Set these to run one after the other, not in parallel. Make sure no other SSIS tasks can run in parallel with this.

2) If that doesn't solve the problem, change the server's MemoryLimitErrorEnabled setting. SSAS predetermines how much memory is going to be required - it sometimes get this wrong. Setting this value to False will prevent SSAS from pre-determining the memory, and it will process the cube anyway. This may still generate a memory error later in processing if there really isn't enough memory, however the chances are it will solve the problem.

If these don't work, then I think your best option is to try SP2, and if it does cause SSIS problems, make sure you've pre-allocated the resource necessary to fix it quickly. I'm running SSIS packages on SP2 without any problems, however I don't swap them between SP2 and pre-SP2 so I'm not sure of the breaking changes.

Hope this is of some use...
btw, more info on MemoryLimitErrorEnabled here
kintonAuthor Commented:
Might be a while looking into that.  Just had a meeting about where the cube is at.  
Speed & Excels ability to return large amounts of data have been brought into question.  Currently our cube is 8gb and its not fast, takes ages to drill down into some of the dimensions and excel often crashes.  However, i've read reports of cubes upto 100gb that are workable, so i've been tasked to strip our cube down to a single fact table, dimension & a couple of measures, fill it with far more data than we have and make it not run slow & crash Excel like the current one does.  I have till next tuesday....Ack!
2 weeks ago I hadn't even seen a cube! (cube has been in dev for longer, but i'm new to the company).
Ouch, good luck!

Couple of things...
1) Speed should not be a problem, speed is exactly what cubes are designed for. However they have to be designed corectly.
2) are you using MOLAP? If not, unless you are doing writebacks etc. MOLAP should be the fastest model for you.
3) try as hard as you can to stick to a star schema - try and avoid snowflakes if at all possible
4) keep it simple
5) make sure you design as many aggregations as you can
6) Excel 2007 has been extended dramatically, specifically for BI/cube/data mining. i.e. among other things it now has >2m rows, compared with 2003's 65k. If you're getting problems with Excel then I'd try 2007 as a test to see if it improves matters.
kintonAuthor Commented:
Excellent thanks PFrog!

We're using Molap, the cubes are read only (I'm guessing write backs are for when the data shown in excel can be edited), we're using Excel 2007, however i'm not sure about the aggregation and the schema.   I think the schema is a star one but i'm not familiar enough with cubes in visual studio or the underlying data to be 100% sure.
That's good.

Star - a fact table sits in the middle, and references a number of dimensions directy.
Snowflake - a fact table sits in the middle, and references a number of dimensions, which also reference a number of dimensions.

Regarding aggregations, if you haven't designed any aggregations, then every time you query the cube is has to run a slow query to find the results in real time.
With aggregations, the results of many/most combinations of dimension members are pre calculated, so that results can just be returned. This single factor provides the majority of the cube's performance enhancements.
kintonAuthor Commented:
Thanks Pfrog i'll have another read of them today I think!  I had glanced over similar documents briefly before I started my job and took another look last week when I first opened a cube in Visual Studio - but I think having a look and putting it in to practice building a cube myself will make much more sense!
kintonAuthor Commented:
As info for anyone else reading this; the cube was failing due to the fact that we had too many cube databases on the server.  We thought they generally didn't take memory up until they were used, and most were versions during the development cycle.

We deleted all that we didn't need and it refreshes all the time now!

As the for original point of the question; we're doing it in roles.  I'm going to try and scipt the roles and hopefully report on them (see post courtest of PFrog; http://www.experts-exchange.com/Database/OLAP/Q_23063566.html).
We've made a couple of mistakes with roles already so really don't like relying on a single person doing them.  Inter-department is risky enough but completely different companies is just plain scary!  

Roles have become the most life threatening thing I deal with; mess them up and I lose my job, can't pay the mortgage or loans, and eventually spiral out of control into a world of depression, crime and vice...... sorry, got a bit carried away there.

Scarface Kinton.
Yesterday - databases
Today - OLAP
Tomorrow - the gutter

such a sad, sad story - it's a warning for all of you young'uns out there contemplating living an exciting, adrenaline fueled life on the edge of IT, it's not all glamour you know...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.