Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to report on historical movements within a changing hierarchy Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 5:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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?
Post #1433548
Posted Wednesday, March 20, 2013 5:48 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
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.
Post #1433549
Posted Wednesday, March 20, 2013 6:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
Never delete. On move, set enddate instead, and copy to new location with startdate=enddate+1. Add date filter to queries.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1433553
Posted Wednesday, March 20, 2013 6:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1433559
Posted Wednesday, March 20, 2013 6:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.
Helen
Post #1433561
Posted Thursday, March 21, 2013 11:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1433945
Posted Thursday, March 21, 2013 9:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
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?
Post #1434121
Posted Friday, March 22, 2013 1:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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).


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1434152
Posted Friday, March 22, 2013 6:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
SQLX, I thought that adding start/end date columns to hierarchy table was essentially the implementation of slowly changing dimension.
Post #1434237
Posted Friday, March 22, 2013 7:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
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.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1434247
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse