Customer Category Problem!

[PaulBirch]

Hi there, I am hoping someone can help me as I am a little stuck here.

I am trying to build a cube which has to include data for around a million customers. The problem is that there is no current classification in the database that I can break these customers down by. So when I add the customers dimension into my model and I build my cube I get the error reporting that a category cannot have more that 65,531 records. This I understand.

To get around the problem I presume that I have to create some categories to break up the customers into some sort of classification. I therefore put a formulae into the IMR/IQD file to calculate the first initial of the customer,
i.e. A-Z.

I then put this into the model as a level, above the customer account level, thinking that this would solve the problem.

In two of the dimensions this has worked successfully and the model builds correctly. However when I add this new level into the final dimension the model will not build the cube. I keep getting an "REPOS-E-NOMEM insufficient memory error". I presume this is something to do with a uniqueness problem as I have lots of memory free and acres of hard disk space.

Does anyone have any suggestions of what I can do to track down/ solve this problem?

The layout of the offending Dimension is:

Sales Office
Rep
CustCat <- New level I have put in with A-Z
Customer Account
Sales Order Number

Thanks in advance.

 

[quatermass]

Hi,

Is it absolutely necessary to go down further than Rep in the cube?

Normally, anything which goes down to line level such as Customer A/C or Sales Order Number I would access via a drill through to Impromptu.

Kevin **************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[PaulBirch]

Unfortunately the customer I am creating this cube for does not have impromptu on the desktop. Also they do need to see the customer level in the cube.

 

[GJParker]

You say that you already have the customer account information in the cube twice already ? Is it really necessary to have it in a third time.

The more times you put this dimension in the cube the largre the cubes will be affecting cube build time and cube response time when open in powerplay.

 

[PaulBirch]

Thanks for your help - I'll try to re-arrange a few dimensions and see if I can fix it.

 

[bsellick]

Just a quick note... you indicated that you need a fair amount of detail in this cube because you don't have Impromptu. Perhaps you should consider a drill through to another cube. You could try having one cube with high-level info and then drill through to a second with lower-level detail.

 

[PaulBirch]

bsellick,

I will certainly try that. It seems to be that the problem is occuring because I have multiple dimensions with customer information in - coming from one data source.

For example some of the dimensions that my client has required are:

Region

Live or Withdrawn customer

Customer Status

Now all the above information is stored in the customer data source. To link the above dimensions to the transaction data source I am using &quot;customer account&quot; as the bottom level of the above dimensions and then setting the level above to be sumarized.

Is this the correct way of doing things? or am I doing something fundamentally wrong.

 

[Pommie]

Hi,

I've had exactly the same set up on a couple of cubes I've 'inherited', although they hadn't reached the magical 65531 limit the model sizes were over 180mb. The solution is to build the customer dimension values in the transaction data source and not bother with the link in Transformer. It increases the size of the data through duplicate columns but it does mean you can build the dimensions without having the customer account included. This will solve some of your problems but if you really need customer account in a dimension you will hit some serious problems in addition to the model size. Have you tried navigating the dimension to expose the customer account number? What was the delay in listing the available members? Another thing to look at is using Alternate Drilldown Paths to the Customer Account.

Hope some of this helps,

Good Luck.

 

[bsellick]

PaulBirch:

I don't think I have enough info to answer your question about doing it right or wrong. I would like to clarify what you are saying regarding the dimensions your client wants and why you would need to duplicate (so it seems) the customer hierarchy in each dimension.

Also, something else that tweaks my spider sense is that you have &quot;Sales Order Number&quot; in the dimension you posted. If anything, I would be concerned about this. This goes back to my other point - and yours - about the amount of detail in the cube. I have also tried, at one point, to include individual orders in an already large cube and we physically could not do so - brought the server down regularly!

Pommie:

I just want to clarify your point. Are you suggesting that the &quot;new dimension level&quot; that PaulBirch created (by customizing the cube model so it seems) should simply be sourced from the data and not done by a model change?

If so, I would agree. However, if I am on the right track, I would suggest PaulBirch go one step further and try to create separate sources for each dimension - outside of the &quot;main&quot; source (what I call the measures source). It makes sense to me, and is also proved by the results I got, that the build process has to do far more work generating dimension categories from a large transaction data source vs. from a few thousand records that only focus on providing categories (and labels) for a dimension.

You would still have a choice to generate categories from the transaction source or not. The reason to still do so is to pick up &quot;orphans&quot; - categories that exist in the transaction data but may not yet exist in your dimension source.

 

[Pommie]

I agree totally with bsellick in that the cube looks like it is going into too much detail, as I mentioned when you have large numbers of categories in a dimension it takes a long time to browse using the dimension viewer. Normally I would have all of the dimensions sourced from individual datasources as Transformer is very efficient when building dimensions joined to the main measure source as I believe that when a dimension is built using the multiple datasources method it never has to check above the lowest level after the first pass, if the dimension is built from fields on the measure source it will check every level every time to ensure uniqueness etc. The drawback in this method is where you have a large range of values for the field being joined, ie customer no. This makes the dimension unwieldy and as has been found you can exceed the 65531 limit quite easily. Looking again at Pauls design requirements customer is needed so you can't get around having one dimension that includes customer. As this appears to work for 1 or 2 dimensions maybe the Alternate Drilldown Paths for the other dimensions, Region Live or Withdrawn customer and Customer Status , is the way to go. Still not going to be pretty though.

 

[Guest_imported]

Hi,

I think the problem is not with creating dimension , it is with database itself. If both the levels in the dimension coming from same table then their is no need to put Cust...in different dimension levels. Instead of that put customer no as one dimension it self and drag the dimension when ever u want to drill down custno level .

Venu