September 22, 2017 at 7:12 am
Hello,
Using SQL Server 2016, VS2015
I have a very complex hierarchy in a Fact.
My client is managing investment funds and lot of different instrument types.
So, each instrument in the fact can be related to multiple parents, and the hierarchy level can reach 5 or more.
Furthermore, the same instrument can be child in different hierarchy trees, different level, can sometimes be parent, etc.
Up until now, we used a combination of DimInstrumentID, DimDateID and DimPortfolioID to rollup the hierarchy tree.
It worked well until we found a case where it's no longer enough.
We have cases where an instrument can be found multiple times at bottom-most level...
All those cases would then have the same immediate parent, but different grandparents.
This messes up the whole thing.
Plus, the difference is not necessarily at grand-parent level, it can be higher in the hierarchy.
Would it be a good idea for the fact to link itself by its own key?
FactAlphaKey
ParentFactAlphaKey
Any ideas to manage that situation?
Thanks,
Vince Poirier
September 24, 2017 at 9:21 am
Can you post some examples of the hierarchies? It's a bit confusing trying to connect the dots without some visuals.
September 24, 2017 at 11:13 am
You are all over the place here. As xsevensinzx said, some examples/visuals would be helpful. Also, a better explanation of what you mean by, "Up until now, we used a combination of DimInstrumentID, DimDateID and DimPortfolioID to rollup the hierarchy tree... It worked well until we found a case where it's no longer enough."
There are a number of ways to build a hierarchy in SQL Server such as nested sets, adjacency lists, materialized/enumerated path or even using hierarchyI'd. None of these methods will work for you unless you have the members of the hierarchy (instrument types in this case) organized into a well-formed hierarchy. This would mean, for example, that leaf nodes with the same parent have the same grandparent too.
Based on what you have said, the best solution would be to have have a different hierarchy tree for each hierarchy in your table. For example, if you look over Jeff Moden's Hierarchies of steroids Part 1: http://www.sqlservercentral.com/articles/Hierarchy/94040/ he describes how to create anew efficient way to manage a single hierarchy tree. If you can arrange your data in a way where each tree is well formed, you would use the methods Jeff details in his article to manage each tree.
-- Itzik Ben-Gan 2001
September 25, 2017 at 10:26 am
Here is some SQL that illustrates my problem :
DECLARE @FactAlpha TABLE (
FactAlphaID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
DimTimeID INT NOT NULL,
DimInstrumentID INT NOT NULL,
ParentDimInstrumentID INT NOT NULL,
UltimateParentDimInstrumentID INT NOT NULL,
DimPortfolioID INT NOT NULL,
AlphaValue DECIMAL(28,6) NOT NULL
)
DECLARE @DimInstrument TABLE (
DimInstrumentID INT NOT NULL,
InstrumentDesc VARCHAR(50) NOT NULL
)
INSERT INTO @DimInstrument (DimInstrumentID, InstrumentDesc)
SELECT 1, 'Fund' UNION ALL
SELECT 2, 'Future on SP500' UNION ALL
SELECT 3, 'SP500' UNION ALL
SELECT 4, 'Apple' UNION ALL
SELECT 10, 'Fund' UNION ALL
SELECT 11, 'Future 1 on SP500' UNION ALL
SELECT 12, 'Future 2 on SP500' UNION ALL
SELECT 13, 'SP500' UNION ALL
SELECT 14, 'Google'
INSERT INTO @FactAlpha (DimTimeID, DimInstrumentID, ParentDimInstrumentID, UltimateParentDimInstrumentID, DimPortfolioID, AlphaValue)
-- Working example
SELECT 20170101, 1, 1, 1, 1, 0 UNION ALL -- Level 1
SELECT 20170101, 2, 1, 1, 1, 0 UNION ALL -- Level 2
SELECT 20170101, 3, 2, 1, 1, 0 UNION ALL -- Level 3
SELECT 20170101, 4, 3, 1, 1, 100 UNION ALL -- Level 4
-- Problematic Example
SELECT 20170101, 10, 10, 10, 1, 0 UNION ALL -- Level 1
SELECT 20170101, 11, 10, 10, 1, 0 UNION ALL -- Level 2
SELECT 20170101, 12, 10, 10, 1, 0 UNION ALL -- Level 2
SELECT 20170101, 13, 11, 10, 1, 0 UNION ALL -- Level 3
SELECT 20170101, 13, 12, 10, 1, 0 UNION ALL -- Level 3
SELECT 20170101, 14, 13, 10, 1, 60 UNION ALL -- Level 4
SELECT 20170101, 14, 13, 10, 1, 40 -- Level 4
-- Impossible to start at Level 4 and properly find our way to Level 1
SELECT L4.*, L3.*
FROM @FactAlpha L4
INNER JOIN @FactAlpha L3
ON L4.DimTimeID = L3.DimTimeID
AND L4.DimPortfolioID = L3.DimPortfolioID
AND L4.ParentDimInstrumentID = L3.DimInstrumentID
Again, my questions :
Would it be a good idea for the fact to link itself by its own key?
FactAlphaID
ParentFactAlphaID
Any ideas to manage that situation?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply