March 20, 2013 at 5:33 pm
I'm in an IT environment where we're wanting to:
1. run queries which will show rates of consumption of entities which are occasionally moving between nodes of a hierarchy
2. AND to correlate the rates of consumption of those entities to the nodes at the same time.
At this time, a senior developer has developed a lookup table of hierarchy paths to correspond to entities, but the moment the entities position in the hierarchy changes, a record of it's old hierarchy path is lost.
What approach would you use to preserve the relationship of an entity with a node path it used to belong to?
--Quote me
March 20, 2013 at 5:48 pm
Record consumption events against nodes the entites belong to at the moment, not entities themselve.
Then shifting an entity to another node won't change statistics of consumption for nodes.
_____________
Code for TallyGenerator
March 20, 2013 at 6:03 pm
Never delete. On move, set enddate instead, and copy to new location with startdate=enddate+1. Add date filter to queries.
March 20, 2013 at 6:33 pm
Vedran Kesegic (3/20/2013)
Never delete. On move, set enddate instead, and copy to new location with startdate=enddate+1. Add date filter to queries.
+100. A "Type 2 Slowly Changing Dimension" will work perfectly here.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2013 at 6:35 pm
K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.
Helen
--Quote me
March 21, 2013 at 11:52 am
polkadot (3/20/2013)
K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.Helen
Check the following link for the different types of SCD's especially Type 2.
http://en.wikipedia.org/wiki/Slowly_changing_dimension
I guess my other question would be, what type of hierarchical structure do you have? Adjacency List, HierarchyID, Hierarchical Path, Nested Sets, or ????
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2013 at 9:25 pm
Hi Jeff,
Thanks for your time.
Answer: Hierarchichal IDs and Hierarchical Paths. Today, the idea of using hierarchyID was tossed, because it was advised one can't join to the hierarchyID table across databases residing on seperate instances (something to do with the binary code column corresponding to hierarchy path perishing when moved across instances and CLR).
So, we are examining use of recursive queries through which we will also be able to generate enumerated paths.
We don't like the idea of using slowly changing dimension and wondering if a better solution.
Here's the scenario: Company manages facilities requests for a bunch of buildings. The hierarchy looks like this: Portfolio-> Region -> Functional Area ->Building (the hierarchy is actually more complex than this). Frequently buildings get reassigned to different functional areas so the parent child relationship between functional area and buildings changes. It is desired that we report on historical trends of facilities requests, for that building, regardless of functional area.
The trouble is that when a subtree is moved, and the table containing enumerated paths is refreshed (via stored procedure to reorder data), the original paths are recalculated to reflect the new parent/child relationships. The slowly changing dimension offered here as a way to persist records of old relationships has been been considered an undesireable solution because of the complexity of queries that would be required to retrieve associated metrics with those entities at each level.
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?
--Quote me
March 22, 2013 at 1:57 am
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?
Yes, solved it with start-end dates and cte packed into a function (instead of a hierarchyid data type).
March 22, 2013 at 6:39 am
SQLX, I thought that adding start/end date columns to hierarchy table was essentially the implementation of slowly changing dimension.
--Quote me
March 22, 2013 at 7:02 am
Name it as you like. Yes, it can be classified as a type of slowly changing dimension, but the name is not really important here. Is that functionality you need or not, can that satisfy your requirement - is.
March 22, 2013 at 9:17 am
Yes, except that my question is
Has anyone else had a similar problem and solved it with something other than slowly changing dimension?
I will create a model of this environment and use it to make concrete headway...I think at this point I need to provide some ddl for there to be a concrete way to help me! But, thanks for your ideas
--Quote me
March 23, 2013 at 7:35 pm
polkadot (3/22/2013)
Yes, except that my question isHas anyone else had a similar problem and solved it with something other than slowly changing dimension?
I will create a model of this environment and use it to make concrete headway...I think at this point I need to provide some ddl for there to be a concrete way to help me! But, thanks for your ideas
No. I don't believe anyone has been able to solve the problem without SCDs other than making a copy of the whole hierarchy. Of course, that's not a bad idea either. Except for the MLM world and huge parts houses, even fairly large hierarchies don't take up much room.
I'll also say that I believe you're making a performance mistake by using recursion rather than storing a nice, tight Nested Sets hierarchy. See the article I pointed you to as to why.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 24, 2013 at 8:46 am
OK, will do. I've got a few more points of reference then I did when you first directed me to that article. So, I'm heading in the direction to ingest and make sense of:
"I'd like to recommend a different approach to hierarchies. Please see the following 2 articles...
http://www.sqlservercentral.com/articles/Hierarchy/94040/
http://www.sqlservercentral.com/articles/T-SQL/94570/"
--Quote me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply