How to report on historical movements within a changing hierarchy

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » How to report on historical movements within...

13 posts, Page 1 of 2

1 2 »»

How to report on historical movements within a changing hierarchy

Rate Topic

Display Mode

Topic Options

Author

Message

polkadot

Posted Wednesday, March 20, 2013 5:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

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

Sergiy

Posted Wednesday, March 20, 2013 5:48 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184

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

Vedran Kesegic

Posted Wednesday, March 20, 2013 6:03 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

Never delete. On move, set enddate instead, and copy to new location with startdate=enddate+1. Add date filter to queries.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1433553

Jeff Moden

Posted Wednesday, March 20, 2013 6:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1433559

polkadot

Posted Wednesday, March 20, 2013 6:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

K, thanks for the leads guys. Anyone want to add, feel free to do so. Appreciate all the input.
Helen

Post #1433561

Jeff Moden

Posted Thursday, March 21, 2013 11:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:51 PM
Points: 32,906, Visits: 26,789

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

Post #1433945

polkadot

Posted Thursday, March 21, 2013 9:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

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

Vedran Kesegic

Posted Friday, March 22, 2013 1:57 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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).

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1434152

polkadot

Posted Friday, March 22, 2013 6:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:00 PM
Points: 172, Visits: 446

SQLX, I thought that adding start/end date columns to hierarchy table was essentially the implementation of slowly changing dimension.

Post #1434237

Vedran Kesegic

Posted Friday, March 22, 2013 7:02 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:08 AM
Points: 342, Visits: 1,072

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.

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1434247

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions