October 2, 2010 at 4:26 am
Hi to all,
I need help for modeling a new DW.
Situation: my ETL software collects measures each quarter from 100 different tlc nodes. The measures are divided in classes (say now there are 50 classes in the ETL). Each measure depends on an OBJ_IDENTITY (.. say 100 Id for each class for each node) and it has a different number of counters. Moreover there is no logical link among classes (in general...)
example for node A:
class_1 = "Node State" ;
Identity (... just one): OWN_NODE
Counters for each identity:CPUCharge, CPUErrors
class_2 = "TLC Route":
Identity:
- Route_to_nodeB_from_A
- Route_to_nodeC_from_A
...
- Route_to_nodeZ_from_A
Counters for each identity:destinationErrors, destinationPackets, destinationSetupTime
class_3 = "Error Code"
Identity:
- FaultCode 1
- FaultCode 2
- FaultCode 3
...
- FaultCode 6000
Counters for each identity:numErrorWithThisCause
class_4 = "Calls":
Identity:
- To_nodeB_from_A
- To_nodeC_from_A
...
- To_nodeZ_from_A
Counters for each identity:num_of_calls, setuptime, len, reject, errors
I was thinking to 4 dimensions: time, node, class, identity. In this case in the fact I will put all counters in anonymous columns (C1, C2, C3...)
But with this solution the user can ask for (D, N, "Error Code", "Route_to_nodeB_from_A" which is clearly a non-sense.
The user must approach the CUBE only choosing fist a type of class (so fixing the dimension Class)
Another approach could be to build 2 DWs:
- The DW for the node measures (class_1 e class_3)
- The DW for the relationship measures (class_2, class_4)
Please suggest me different points of view
Riccardo
October 4, 2010 at 5:57 am
riccardo.russo79 (10/2/2010)
I need help for modeling a new DW.Situation: my ETL software collects measures each quarter from 100 different tlc nodes. The measures are divided in classes (say now there are 50 classes in the ETL). Each measure depends on an OBJ_IDENTITY (.. say 100 Id for each class for each node) and it has a different number of counters. Moreover there is no logical link among classes (in general...)
example for node A:
class_1 = "Node State" ;
Identity (... just one): OWN_NODE
Counters for each identity:CPUCharge, CPUErrors
class_2 = "TLC Route":
Identity:
- Route_to_nodeB_from_A
- Route_to_nodeC_from_A
...
- Route_to_nodeZ_from_A
Counters for each identity:destinationErrors, destinationPackets, destinationSetupTime
class_3 = "Error Code"
Identity:
- FaultCode 1
- FaultCode 2
- FaultCode 3
...
- FaultCode 6000
Counters for each identity:numErrorWithThisCause
class_4 = "Calls":
Identity:
- To_nodeB_from_A
- To_nodeC_from_A
...
- To_nodeZ_from_A
Counters for each identity:num_of_calls, setuptime, len, reject, errors
I was thinking to 4 dimensions: time, node, class, identity. In this case in the fact I will put all counters in anonymous columns (C1, C2, C3...)
But with this solution the user can ask for (D, N, "Error Code", "Route_to_nodeB_from_A" which is clearly a non-sense.
The user must approach the CUBE only choosing fist a type of class (so fixing the dimension Class)
Another approach could be to build 2 DWs:
- The DW for the node measures (class_1 e class_3)
- The DW for the relationship measures (class_2, class_4)
Please suggest me different points of view
I would go neither with anonymous-columns nor with "two dw" solution.
Not knowing much about the project is a little hard to have an educated opinion but how about building one datamart per class? I can picture fact tables being fact_node_state, fact_tlc_route, etc.
Just a different point of view.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 4, 2010 at 7:58 am
Paul,
your solution is nice. I will have 50+ classes... so I will have 50+ DW with 1 DataMart each one?
What about teh dimension:
- TIME
- NODE
- IDENTITY
- TYPE (degeneated)
Is this correct for you?
Riccardo
October 4, 2010 at 5:25 pm
riccardo.russo79 (10/4/2010)
Paul,your solution is nice. I will have 50+ classes... so I will have 50+ DW with 1 DataMart each one?
I would say it will end up being one DWH with as many datamarts as Classes are.
riccardo.russo79 (10/4/2010)
What about teh dimension:- TIME
- NODE
- IDENTITY
- TYPE (degeneated)
Not knowing much about the project it sounds good. I can picture a bunch of Dimensions being shared by many datamarts like DIM_TIME.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 3, 2010 at 12:51 pm
Hard to completely understand your problem, but:
Create a Class Dimension. ClassKey will be included into your fact table which will indentify which measure is being used.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply