on 06-03-2011 4:32 PM
Hello,
In BI Systems, the way to group products of the product dimension is not easy:
you can place manually a product in a group each time that a product is created -> it takes a lot of time
or you can create a rule using the attributes of the products to place automatically the product in the group
ex: my products are "cars" and I have the attribute "color" and number of doors (2 or 4)
I create a group with several values (ex: "blue 2 doors", "green 4 doors",...)
so I can have a rule to place automaticlly my cars into may groups
ex: color = blue and nb doors = 2 -> group "blue 2 doors"
color = blue and nb doors = 4 -> group "blue 4 doors"
etc....
my question:
if the rule is placed in the data base and that the group to which the products belongs is calculated in the database, it's expensive to do a change of structure of the rule (ex: i want to use the size of the car to slit my groups)
-> is there a solution to handle rules and automatic assignement of a product to a group into BO XI (like this, if there's a change to do, only the univers is impacted, not the database and we win in reactivity and build effort)
thanks in advance for your answer
if yu think that my question is not enought technical for this forum could you move it into the appropriate forum please ?
thx
dansledoute
Hi
I will not recommend storing the product and Product group association in Universe (Though I am not sure how that can be done if possible).
Solution would be Have a Product group table as below
Group_Dim
PK | Color | No OF Doors | Attribute 2 | Attribute 4 | .....
------------------------------------------------------------
1 | Blue | 2 | Some Val1 | ...
2 | Blue | 4 | Some Val1 | ...
3 | Red | 2 | Some Val1 | ...
4 | Red | 4 | Some Val1 | ...
And the use group PK as foreign to Product or Add directly to your Fact as foreign (Recommended)
Product Dim
PK | Product | GroupFK
=======================================
1 | Car1 | 2
2 | Car2 | 3
All this should go in you Data warehouse design
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello
@ Me map
the idea is to apply a rule on different attributes of my product to have an other attrubutes and I would like if possible to do this in the universe and not in the database
@ Kuldeep Chitrakar
Thanks for the answer,
so you think that this is something to do in the database and not possible in the universe,...
This is actually the case in our systme but we have a lot of change in our groups and so I have to ask for an upgrade of the Database and the Univers
I understand know why some sofware are specialized in the Product dimension (classification, life cycle,...)
-> BO is not the good BI tool to have advanced features in the product dimension
I don't know if I clearly understood your question
In fact, my production dimension is synchronized with the global SAP system of the company (and to change anything in SAP is quite impossible because too expensive),...
some groups are presente in SAP but not all the groups that we use i ou BI system for our reports,... so we create our own groups,...
when there's an organisation cahnge in the company or a new way to analyse data, the business can ask to change all the groups very quickly. To avoid a manual assigment each day to our group, some automatic rules have been created in the database (ex: if product has A1 = blue and A2 = big so A3 = Alpha -
A1 and A2 are SAP data, A3 is our BI group)
but if we have to change the rules of our groups (using other attribute. ex: A3 is not anymore calculated with A1 and A2 but with A1, A2, A6, A8 and A9), then we have to do changes in the database (not in the univers because it read directly the data calculated and stored, I agree)
but the change in the database is much more heavy than a change in the univers,...
that's my problem, but if you says that this is the better thing to do than to modify the database structure, I trust you and my problem is resolved,....
do you understand ?
My solution was at the universe level.
You could create whatever objects you need for grouping any columns together by concatenating them.
Object 1:
Doors || Colour
Now you decided to have it grouped by fuel type too.
So, Object 2:
FuelType || Doors || Colour
If you want to just go by fuel type and doors then....
Object 4:
FuelType || Doors
If that's not what you want then you may need to clarify your requirements.
Regards,
Mark
In fact,
the concatenation is not th value that I want to see display
if I have "blue" and "car", I want to have "cool"
when you says to use "blue" || "car", you have "bluecar" and not "cool"
or maybe you talk about a way to do a translation rule in the universe using concatenation ?
ex: "bluecar" => "cool"
I assume you have product attribute associated with products
e.g.
Product | Doors | Color | Attribute 3
XYZ | 1 | Blue | ----
ABC ----
1. Create a Derived table per attribute as
e.g.
Doors_D
Select 1 as NoOfDoors From Dual
UNION
Select 2 as NoOfDoors From Dual
UNION
Select 3 as NoOfDoors From Dual
UNION
Select 4 as NoOfDoors From Dual
Color_D
Select Blue as color from dual
UNION
Select Red as color from dual
UNION
Select Green as color from dual
Create derived table per attribute
3. Now create another derived table as
Custom_Grouping
select A.NoOfDoors,B.color, (CASE WHEN (NoOfDoors=1 and color='Blue') THEN 'COOL'
WHEN (NoOfDoors=2 and color='Red') THEN 'ExtraCool'
END) Group1
FROM Doors_D A, Color_D
4. Now you have attribute and group of attribute now you can join Custom_Grouping with product using product attribute column so that the product gets its intended group. now if you ever need to change the group say
NoOfDoors=1 and color='Blue' Is not 'Cool' but its now called as 'CoolCar'
You just need to change the Custom_Grouping at universe level
--Kuldeep
Ah, got you now.
It's very easy but unfortunately you will have to maintain the categorisations whichever way you go. The non-database approach would be to create a new object
CASE
WHEN car_dim.colour='blue' and car_dim.doors = 2 THEN 'Cool Small'
WHEN car_dim.colour='blue' and car_dim.doors = 4 THEN 'Cool Big'
WHEN car_dim.colour='red' and car_dim.doors = 2 THEN 'Hot Small'
ELSE 'Uncategorised'
END
You can then add any other objects in a similar manner.
If I understand your question correctly then I think you would be able to create a concatenated option in your universe:
table.car_type ||' '||table.no_of_doors
or
table.car_type +' '+table.no_of_doors
for example
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.