too many dimensions - after trying all the golden Kimball rules

View previous topic View next topic Go down

too many dimensions - after trying all the golden Kimball rules

Post  element on Wed Mar 16, 2011 5:42 pm

I'm creating star schemas on top of SAP transactions (sales order, outbound deliveries, etc.)

together with the customer we've identified a large set of dimensions based on identified business requirements; Between 30 and 50 for each fact table on average. I've checked whether certain 'dimensions' are not attributes of other dimensions, which isn't the case. I've grouped a number of small dimensions together in junk dimensions.

What else can I do to limit the number of dimensions?

thanks so much!
Kris

element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  ngalemmo on Wed Mar 16, 2011 6:03 pm

I've created dimensional warehouses from SAP data a number of times in the past and do not recall having so many dimensions for a single fact. What are the dimensions?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  element on Wed Mar 16, 2011 6:29 pm

to give you an example for the sales order fact (combination of order headers & order detail lines);
1 - sales order date
2 - pricing date
3 - customer PO date
4 - sales order item created date
5 - sales order item created time
6 - calculated requested delivery date
7 - sales organization
8 - sold to customer
9 - ship to customer
10 - payer to customer
11 - bill to customer
12 - account manager
13 - material
14 - plant
15 - storage location
16 - route
17 - local currency
18 - usage
19 - delivery block
20 - shipping conditions
21 - special processing
22 - tax departure country
23 - tax destination country
24 - incoterms
25 - billing block
26 - payment terms
27 - profit center
28 - rejection reason
29 - partial delivery / has return items / is complete delivery --> junk dimension
30 - shipping point
31 - special stock
32A, B, C ... - 3 different texts (I know ...)
33 - header statusses (7 in total - combined in 1 junk like dimension)
34 - detail statusses (7 in total - combined in 1 junk like dimension)
35 - header created date & time
36 - item created date & time
37 - employee header & detail created user

A number of these dimensions are code & descriptions (4 - languages) only.
All of dimensions and their attributes are required in either reports/analyses as information or as potential filters and parameters.

Please advise - or share you earlier created data model on this.

thanks so much!
Kris

element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  gvarga on Wed Mar 16, 2011 7:00 pm

There are a lot of so called role-playing dimensions ( Date, Customer, Country) .
Normally you have to create one Date or one Customer dimension table.
Of course you can create role playing views for the various candidate Date dimensions as follows:

CREATE VIEW SALES_ORDER_DATE (ORDER_DATE_KEY, ORDER_MONTH....)
AS SELECT DATE_KEY, MONTH..... from DATE


gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  element on Wed Mar 16, 2011 8:47 pm

thanks gvarga for pointing this out.

However, I have already taken this into account; I will create database views or add logical dimensions in the front-end end-user-layer for that matter. But I still will have either logically in the end the same number of dimensions in the front-end tool or physically I will still have the same number of (foreign) surrogate keys in the fact table.

So the question remains here ...

any other ideas ... ? ngalemmo?

Kris

element

Posts : 6
Join date : 2011-01-28

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  John Simon on Wed Mar 16, 2011 10:21 pm

Couldn't you make the SoldTo, ShipTo, BillTo, PayToCustomer records into one dimension?


John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  Jeff Smith on Thu Mar 17, 2011 8:48 am

Identify the dimensions that will not be used with other facts and see if they can be grouped into one or more junk dimensions.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  BoxesAndLines on Thu Mar 17, 2011 11:03 am

Go with 50 and see if the performance is there. Be judicious in your FK index creation. Some facts just have a large amount of dimensions. What I have found is that a large amount of those dimensions are needed for "just in case I need it". The performance using those dimensions are not as critical as the higer value dimensions.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  BoxesAndLines on Thu Mar 17, 2011 11:04 am

John Simon wrote:Couldn't you make the SoldTo, ShipTo, BillTo, PayToCustomer records into one dimension?


Those are one dimension, they're just role named. At least they should be.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  ngalemmo on Fri Mar 18, 2011 2:48 pm

I would question wither the dimensions apply to all facts as you seem to indicate.

For example, looking at sales orders, there are orders, shipments and invoices. Three fact tables, an not all dimensions apply to each one. I would not know why payer would be a dimension relating to orders or shipments, or even invoices for that matter (AR recipts is a more likely place). In a lot of cases, there is information SAP passes through the process that only has bearing on one part of the process.

Does someone really need to do analysis of orders by payment terms... or do they really want to look at invoices by payment terms? Do shipping conditions really only apply to the shipping facts?

You need to do some soul searching with the business to understand (or help them understand) what analysis they want to do. I am not advocating thowing away good information, it is just that when dealing with COTS ERP packages like SAP, you need to tailor what you use based on your business processes.

In addition there may be some further consolidation of dimensions possible. For example, could not 'special stock' be attributes of material?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: too many dimensions - after trying all the golden Kimball rules

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum