SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to report on historical movements within a changing hierarchy


How to report on historical movements within a changing hierarchy

Author
Message
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1112
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?
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 11960
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.
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85268 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1112
K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.
Helen
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85268 Visits: 41078
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1112
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?
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

polkadot
polkadot
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1112
SQLX, I thought that adding start/end date columns to hierarchy table was essentially the implementation of slowly changing dimension.
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search