November 11, 2011 at 2:19 am
Hi,
I have a organization table below which shows the relationship between a parent and child relationship of a organization hierarchy.
[Organization table]
Parent Organization
--------------------------
Sunway Group
Sunway Group 10000000
Sunway Group SUNH GROUP
SUNH GROUP 21000000
SUNH GROUP 22000000
SUNH GROUP 23000000
I have a fact table which links to the organization code. Now, I intend to select all organizations from the fact table by only using 'Sunway Group' as the keyword.
Example:
select * from fact_table where organization in ('2100000','2200000','2300000')
which is equivalent to: select * from fact_table where organization = 'Sunway Group'
but the problem is that the fact table only stores base level organization members.
So, my question is how to select all organizations from the fact table by only using 'Sunway Group' as the keyword?
Thanks!
November 11, 2011 at 6:35 am
Hi yingchai
I need more elobration on this table... means if u can provide more data i can get clear picture about it.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
November 11, 2011 at 7:55 am
If I'm understanding correctly, it looks like you're trying to obtain all of the descendants of a particular organization in a hierarchy. One way to handle that is with a recursive CTE. Below is an example of how this can be made to work:
USE tempdb
GO
CREATE TABLE #orgs
(
ParentOrgName VARCHAR(15) NULL,
OrgName VARCHAR(15) NOT NULL
)
INSERT INTO #orgs (ParentOrgName, OrgName)
SELECT NULL, 'Sunway Group' UNION
SELECT 'Sunway Group', '10000000' UNION
SELECT 'Sunway Group', 'SUNH GROUP' UNION
SELECT 'SUNH GROUP', '21000000' UNION
SELECT 'SUNH GROUP', '22000000' UNION
SELECT 'SUNH GROUP', '23000000' UNION
SELECT NULL, 'Starlight Club' UNION
SELECT 'Starlight Club', '30000000' UNION
SELECT 'Starlight Club', 'Local Group 1' UNION
SELECT 'Local Group 1', '41000000' UNION
SELECT 'Local Group 1', '42000000';
-- Set the value of @ParentOrgName to the organization
-- whose heirarchy we wish to obtain
DECLARE @ParentOrgName VARCHAR(15);
SET @ParentOrgName = 'Sunway Group';
-- Declare a recursive CTE
WITH OrgTree
AS
(
-- Root member definition (Parent)
SELECT p.ParentOrgName, p.OrgName, 0 AS OrgLevel
FROM #orgs AS p
WHERE p.OrgName = @ParentOrgName -- Start with the parent org
UNION ALL
-- Recursive member definition (traverse Child nodes)
SELECT p.ParentOrgName, p.OrgName, c.OrgLevel + 1
FROM #orgs AS p INNER JOIN
OrgTree AS c ON p.ParentOrgName = c.OrgName
)
-- Return the whole heirarchy starting with @ParentOrgName
SELECT *
FROM OrgTree;
DROP TABLE #orgs
In order to make this more usable, you can wrap the recursive CTE inside an inline table-valued function that takes @ParentOrgName as an input parameter and returns the parent and child members as a table. I'll post an example of that next.
WARNING: Recursive CTE's aren't the most efficient things in the world, and if you have a very deep heirarchy, performance could be a real issue. There are other ways of solving this problem, but for a hierarchy table with this kind of recursive structure, they're the simplest way I know of at the moment. I'm sure there are others here who can point out a more efficient solution though. 🙂
November 11, 2011 at 8:19 am
Ok, the next step: creating a Descendants ITVF.
First, create the table with test data, and create the ITVF:
USE Sandbox -- Use whatever DB you want, this is just my "playing around" DB
GO
-- Create testing Organizations table
CREATE TABLE Organizations
(
ParentOrgName VARCHAR(15) NULL,
OrgName VARCHAR(15) NOT NULL
)
-- Insert sample data into Organizations
INSERT INTO Organizations (ParentOrgName, OrgName)
SELECT NULL, 'Sunway Group' UNION
SELECT 'Sunway Group', '10000000' UNION
SELECT 'Sunway Group', 'SUNH GROUP' UNION
SELECT 'SUNH GROUP', '21000000' UNION
SELECT 'SUNH GROUP', '22000000' UNION
SELECT 'SUNH GROUP', '23000000' UNION
SELECT NULL, 'Starlight Club' UNION
SELECT 'Starlight Club', '30000000' UNION
SELECT 'Starlight Club', 'Local Group 1' UNION
SELECT 'Local Group 1', '41000000' UNION
SELECT 'Local Group 1', '42000000';
GO
-- Create the ITVF, which encapsulates the recursive CTE
CREATE FUNCTION Descendants (@ParentOrgName AS VARCHAR(15))
RETURNS TABLE
RETURN
-- Declare a recursive CTE
WITH OrgTree
AS
(
-- Root member definition (Parent)
SELECT p.ParentOrgName, p.OrgName, 0 AS OrgLevel
FROM Organizations AS p
WHERE p.OrgName = @ParentOrgName
UNION ALL
-- Recursive member definition (traverse Child nodes)
SELECT p.ParentOrgName, p.OrgName, c.OrgLevel + 1
FROM Organizations AS p INNER JOIN
OrgTree AS c ON p.ParentOrgName = c.OrgName
)
SELECT OrgName, ParentOrgName, OrgLevel
FROM OrgTree
GO
Ok, now that's done and we can take it for a test drive:
-- Select Sunway Group and all of its children
SELECT *
FROM dbo.Descendants('Sunway Group');
-- Select only the children of Sunway Group
SELECT *
FROM dbo.Descendants('Sunway Group')
WHERE OrgLevel > 0;
-- Select only the immediate children of Sunway Group
SELECT *
FROM dbo.Descendants('Sunway Group')
WHERE OrgLevel = 1;
-- Select Sunway Group and its immediate children
SELECT *
FROM dbo.Descendants('Sunway Group')
WHERE OrgLevel <= 1;
-- Select Starlight Club and all of its children
SELECT *
FROM dbo.Descendants('Starlight Club');
You can then join your fact table to this ITVF and pass in the parent org you're interested in.
November 11, 2011 at 8:28 am
Given the scenario, this sounds like a homework question.
November 13, 2011 at 7:30 am
Brandie Tarvin (11/11/2011)
Given the scenario, this sounds like a homework question.
You may be right 🙁
November 16, 2011 at 7:53 pm
Hi JonFox,
Thanks for the detailed reply...really appreciate it. This is not a homework question. I am currently working in a BI project and users want to drill a report to a detail transaction level by lowest level company or intermediate level. Currently, I am not sure how to drill via intermediate level which is why I post my query here.
Anyway, thanks and I will try it later 🙂
November 17, 2011 at 4:45 am
Well I'm always happy to help in that case. 🙂 Let me know how it works for you. Are you utilizing Analysis Services by any chance? I believe SSAS has a feature that makes navigating ragged hierarchies easier...
November 17, 2011 at 4:48 am
Both SSAS and SSRS have drill down functionality that makes things easier for you without having to code all the T-SQL. In fact, with SSRS, you can use subreports for your drill downs, making your code even more simple.
The reason why I thought it was a homework question is that business rarely require a developer use a specific keyword in their code. That seemed a little odd to me and something a teacher might dictate, not a real world business need.
November 17, 2011 at 8:00 am
I am not using SSIS or SSRS either. I am using a BI tool from Infor...not sure you guys have heard of that before. Currently, all my reports are generated from cubes (aggregated data). But there are some low-level data which are stored from a relational db. Hence, I am trying to use the dimension members from the cubes and drill further down to the relational table.
Just tried the scripts and it works...many thanks!
November 17, 2011 at 11:28 am
yingchai (11/17/2011)
Just tried the scripts and it works...many thanks!
Great! Make sure to do some serious testing before putting this into production though, recursive CTEs can really get away from you...I can't guarantee this is the best/safest approach for your situation, just that it's an approach! 😀
November 24, 2011 at 7:58 am
Hi JonFox and other SQL Gurus,
After I had done some serious testing on recursive CTE, I found out that my report performance is quite bad when I switch from one intermediate organization group to another group...the report results took around 40 seconds to refresh. Is there any way to improve the performance of this recursive?
Thanks.
November 24, 2011 at 8:15 pm
It's going to depend on a few factors, such as the depth of your hierarchies, your indexes, and so forth. Can you post the actual execution plan for the long running query? I won't be able to look at it until Sunday at the soonest, but I'll look it over as soon as I can.
November 24, 2011 at 10:56 pm
Hi JonFox,
What do you mean by the 'actual execution plan for the long running query'? Are you referring to the whole actual sql script that I'm executing?
* Please do not feel obliged to look at this matter as soon as possible...do it on your own free time coz I am not rushing for it.
Thanks!
November 25, 2011 at 7:46 am
Here's some info on generating the actual execution plan when running a query from SSMS: http://msdn.microsoft.com/en-us/library/ms189562.aspx. Once the plan has been generated, you can right click in the plan window and select Save As... and save it as a .sqlplan file. If you can do that and upload the file here as an attachment, as well as the full query that the plan is for, I'll take a look as soon as Im done with the holidays. Thanks, and you're welcome!
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply