July 23, 2009 at 3:37 am
Hello,
This is the first time that I will use "bridge table" in my architecture
of datawarehouse. I don't know if it will be the same as which in OLTP relational structure.
I have a product dimension as:
IDPRODUCT(PK) DESCRIPTION IDPARENT
I have two fact tables, one for the measure of product and the other for the measure of clients.
FactProduct:
IDCLIENT IDPRODUCT PRODUCTNUMBER MONTANT...(and the dimension keys)
FactClient:
IDCLIENT AGE ANCIENTY INCOME
I want to join that 2 fact tables as using a bridge table..
Would u please help me how to do??Will I put CLIENTID and PRODUCTID in that table?? Is it enough?
thanks
July 23, 2009 at 6:56 am
ok I've solved my problem that i will use an intermadiare fact table which has only IDCLI column. That will allow me to know to acces to the measures of clients who has the different product
July 23, 2009 at 8:22 am
Just out of interest, the table details you listed for Client fact didn't appear to have *any* facts, the age, ancienty and income are all attributes of the client (possibly changing over time, but again this can be handled in a dimension). It would appear that the 'client fact' could simply be used as a dimension against the 'product fact'.
Steve.
July 23, 2009 at 8:43 am
In fact I've understood the thing taht you proposed, thanx for your interest but this is not my case. Because the detail of client it isn't interesting for the department marketing(I do a project for the departement marketing of a bank). It is important for exemple the moyen age of the client who have the mastercarte...smthing like that but this is a good remark. The first time that I've seen the datas of the departement my reflex was like that ,too. But their demands are different
thanx
July 23, 2009 at 9:18 am
Alors que vous souhaitez utiliser certains des attributs que les mesures et les attributs peut-être aussi? Si tel est le cas, vous pouvez faire acte de la table à la fois comme un fait et de dimension, vous n'avez pas besoin d'un pont.
De cette façon, l'âge moyen sera créé, en mettant l'âge dans la colonne de mesure, mais l'âge peut également être utilisé comme un attribut de filtrage.
Steve.
July 23, 2009 at 9:41 am
D'abord je n'ai pas compris comment vous avez su que je parle francais mais merci c plus facile:)
Alors encore je vous comprends ,vous avez tout à fait raison.En faite c'est vrai j'ai une table de dimension nommé "tranche d'age" il ya des attributs 0-17, 18-25..dans cette tableau. Mais à la fois je m'interesse a la mesure "age" parce que c'est important pr les etudes des experts de marketing. Mais en faite j'ai pas tous mis mess mesures dans mon premier message à mon FACTCLIENT. Mais j'ai des mesures "montant facturation, age entré au banque, age, nombre partenaire et montant" il sont des données directs des clients...
Mais la structure dans mon tableau FACTPRODUCT c'est totalement different. Je repete des enregistrements des clients por chaque produit different et il ya bcp de clients qui ont plusieurs produits et le banque il a bcp de produit aussi:) Donc si je mets tous les mesures de FACTCLIENT dans tableau de FACTPRODUIT alors je sera obligé de repeter ces mesures pr caheque enregistrement de chaque produit d'un client. Ca pose un probleme de volume. C pour ca en faite j'ai fait mon architecture comme ca...qu'est ce que vous en pensez?
July 23, 2009 at 7:16 pm
That's just soooooo freakin' rude, dudes.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2009 at 8:47 pm
@jeff - Are you serious?
What i said ->
So you would like to use some of the attributes as measures and maybe attributes also? If so, you can make the table act as both a fact and dimension, you still do not need a bridge.
That way, average age will be created by putting the age column in as a measure, but age can also be used as an attribute for filtering.
The reply ->
First I did not understand how you know that I speak french but thank you it makes it easier 🙂
I understand you, you are quite right. In fact it's true I have a dimension table named "age bracket" there are attributes 0-17, 18-25 .. in this table. But at the same time I am interested in measuring "age" because it is important for the study/work done by the marketing experts. But actually I did not list all of the measures that are in FACTCLIENT in my first message. But I measure billing amount, age joined the bank, age, partner(account) number and amount. All of this data is derived from the customer/s...
But the structure in my table FACTPRODUCT is completely different. I repeat records for clients for each different product and there are a lot of customers who have multiple products and the bank has bcp products also 🙂 So if I put all the measures in FACTCLIENT in to the table FACTPRODUCT then I will be repeating these for each product for each client. This poses a problem of volume. [Earlier/before] I actually did my architecture like that ... what do you think?
My latest reply -> (En anglais seulement)
My suggestion wasn't to add the measures to the ProductFact table, but for the Client dimension to act/participate in the cube as both a fact and a dimension. However I can see that this still will be a problem due to double/triple counting. I would recommend reading the 'Many to many Revolution' whitepaper by Marco Russo (here) as I think he covers (at least one) extremely similar scenarios and has downloadable examples that go along with this.
Steve.
July 23, 2009 at 8:57 pm
What Jeff was complaining about was the sudden change in language in the thread. Using French left the rest of out of the loop on what was going on regarding the problem at hand.
It would have been different had we been experiencing the problem we ran into on another thread where it turned out the poster was using BableFish to translate Spanish into English for posting then using BableFish to translate the English replies back to Spanish. Much was lost in the translation. In cases like that going to the OP's native lanaguage makes more sense.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply