Adjacency Matrix In MDS With A Recursive Derived Hierarchy?

Apr 30, 2014

If you've ever worked with CI (UPC barcodes), certain commercially distributed products can be "packaged" in certain ways as defined by suppliers. For example, buying a six-pack of Coca-Cola, each one of the cans have a barcode UPC that indicates that the particular can is to only be distributed as part of a "pack" - they can't be distributed or sold individually.

Certain products can be distributed and sold in more than one way. For example, that same six pack can be distributed and sold as a case bundle, or it can be distributed as a single six pack. Each packaging method has a certain hierarchy.you can't represent the CI model using anything less than an adjacency matrix.Now, this can be represented in SQL pretty easily - there's tons of examples online that go through the basics of how to do it and how to write the recursive CTE function to represent and return data in this format. My problem is with MDS.

In Master Data Services (MDS) you have several hierarchy types to choose from. The hierarchy type that makes the most sense to me is to use the recursive derived hierarchy. It makes sense because I want to define an entity that contains all the "acceptable" adjacencies for particular items, such that when a user looks at the data in MDS or tries to update its value, it will have certain domain-based enforcement (and thusly lookups).I think that I've crossed a bridge too far with MDS - but, perhaps one of you knows something I don't know. It looks like any hierarchy in MDS is only based in a parent - child relationship, so that's not quite what the doctor ordered here.

View 0 Replies


ADVERTISEMENT

SQL Server 2008 :: Converting Adjacency List To Hierarchy Table

Feb 10, 2015

I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.

My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.

select * from SourceTable -- produces 22461 records

--step 1: first, get row number of child records in each parent

SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;

[Code] ....

View 1 Replies View Related

What Does A Recursive Hierarchy Offer That Is Different From Just Inserting Another Group?

Dec 3, 2007

I noticed that by inserting another group higher than the existing one gave me the effect of a nested group...so what would a recursive hierarchy have done for me that is different? I read the BOL writeup and didnt come away with any sense of what these hierarchies offer, seemingly thru the use of a "parent" entry.

View 1 Replies View Related

Excel Export Problem - Flattened Recursive Hierarchy

Feb 1, 2008

Hi,

I have a recursive hierarchy in SSRS2005 which contains accounts like so:

+ Revenue
+ Expense

which is grouped using details group and can be expanded like:

+ Revenue
- Revenue type
- Revenue type
- etc.
+ Expense
- Expense type
- Expense type
- etc.

It can be drilled down in five levels. This works fine in both SSAS and SSRS, but when I export the report to excel 2003, the hierarchy gets flattened, meaning that every account on the detail level (level 5) is exposed. Not what I had in mind. Rather SSRS should create a hierarchy in excel as well..

Is there a workaround for this?

View 3 Replies View Related

Initial Visibility Expression Stays In Effect On A Recursive Hierarchy/drilldown App

Dec 10, 2007

does anyone know why my initial visibility expression (=Level()>1) stays in effect after generating my report? The idea was to display only a couple of levels in the hierarchy at first, so that the user could more easily decide what he wants to expand. But expand doesnt do anything after initial rendering.

The only way I could get this kind of report to work (show hierarchy and expand/collapse) was to live with initial visibility = "visible", but unfortunately, that setting shows all levels right away, not a very user friendly thing.

I am running Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

View 1 Replies View Related

Master Data Services :: Derived Hierarchy - No Levels Defined

Aug 31, 2012

Out of nowhere my derived hierarchies starting showing the following message in the MDS UI

No Level Defined:  This Derived Hierarchy is incomplete......

As you can see below the structure is defined and still in tact.  This message shows up in by the Explorer & System Admin areas.  I'm also able to query the subscription view setup without any issue.

This is with 2012 w/ no CUs.  Same setup in running in another environment without issue.

View 5 Replies View Related

Matrix SSAS Dimension/hierarchy Order Problem

Oct 4, 2006

I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.

The report is somehow treating the Month like character data instead of numeric, how do I fix this?

View 4 Replies View Related

Adjacency List From SQL Table To XML File

Nov 19, 2007

I'm having trouble generating an XML file from a sql table. Basically its an HR Dept. table that has departmentID and ParentDepartmentID. I need to create a hierarchal xml file that has a parent node, and all its departments, and if any of the sub departments have any below them, stick those departments in accordingly. I can do this two ways...either through tSQL or through .NET recursive methods. I've tried the recursion but can't wrap my head around it enough to get it working.

Here is the table schema:




Code BlockCREATE TABLE [dbo].[DepartmentTree](
[DepartmentID] [varchar](13) NOT NULL,
[ParentDepartmentID] [varchar](13) NOT NULL,
[Description] [varchar](30) NOT NULL,
[ManagerEmployeeID] [varchar](13) NOT NULL
) ON [PRIMARY]





here is the xml markup I'm looking for (or facsimile thereof...doesnt really matter)




Code Block
<?xml version="1.0" encoding="UTF-8" ?>
<Department ID="1" Name="IT" Manager="12345">

<Department ID="2" Name="IT Info. Sys" Manager="23456" />
<Department ID="3" Name="IT Technical" Manager="34567">

<Department ID="4" Name="IT Technical West" Manager="45678" />
<Department ID="5" Name="IT Technical East" Manager="56789" />
</Department>
</Department>




Here is my test data:





DepartmentID
ParentDepartmentID
Description
ManagerEmployeeID

1
1
IT
12345

2
1
IT Info. Sys
23456

3
1
IT Technical
34567

4
3
IT Technical West
45678

5
3
IT Technical East
56789



Does anyone have a suggestion as to how to do this?

View 5 Replies View Related

Transact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query

Aug 25, 2015

Msg 240, Level 16, State 1, Line 14

Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,

DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),

[code]....

View 2 Replies View Related

How To Convert Recursive Function Into Recursive Stored Procedure

Jul 23, 2005

I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod

View 4 Replies View Related

Integration Services :: How To Declare Multiple Derived Column In SSIS Derived Column Task

Jul 22, 2015

how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.

View 4 Replies View Related

Reporting Services :: SSRS Matrix - Add A Column In A Matrix With A Variance

Aug 6, 2015

I got the following code to add a column in a matrix with a variance:

IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)

This code works fine, except that the first row of the matrix shows an #error

This happens with each matrix where I use this expression. A warning emerges:

rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.

Attempted to divide by zero.

The strange thing is that the part

Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'

An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)

As you can see, all other 2013 rows show a blank cell, except the first row.

View 3 Replies View Related

A Matrix Above Two Charts. Right Chart Moves Depending On Matrix Growth ... ???

Jan 10, 2008

I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.

Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.

How? Why does the right chart move?

Thanks in advanced
Kind Regards

View 1 Replies View Related

Matrix - Create Two Rows In A Matrix

Nov 26, 2007

Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.

The data should look like this:

10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....

Thanks for any help.

View 3 Replies View Related

Filtering Through Matrix Or Groups In Matrix

Aug 21, 2007

I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.

View 1 Replies View Related

Adding Columns To A Matrix Report That Don't Belong To The Matrix Columns Groups

Jan 2, 2007

Can we do this?



Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦



That is, for example, having the following report:

Product Family


Product

Country City Number of units sold





Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦




Any guidance
on that?


The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦






Many
thanks,
Jose

View 4 Replies View Related

Second Matrix Blanks Out First Matrix

Jun 8, 2007

Hello,



I have a matrix in my report that is based on an MDX query. I copy-paste the matrix and then see the two matrices in the report and all is fine.

I then insert a group into the second matrix. When I run the report the second matrix appears as I would expect, but the cells are blank in the first matrix. Sometime all the data cells are blank, but not the totals.

I tried a similair thing with a table. I added a table based on the same MDX dataset and the cells in the matrix were also then blanked out.



I made sure that the matrices were not sitting on top of each or even in the same space.



The goal here is to show the same dataset but with different groupings. I thought of doing drilldowns, drillthroughs, etc. but this is the way they want to see the data.



Any ideas what I am doing wrong?



Thank you for the help.



-Gumbatman

View 4 Replies View Related

Db Hierarchy

May 8, 2006

HI!
I am new in DB so I need some advices for finding the right solution.
I need to be able to make automatically any join between tables which the user choose and deliver the result.
is like making an hierarchy between all tables in DB (parent-child) and then making the select statement for the right join.

if u have any idea about how can I manage this, pls help me.

thanks a lot!

View 2 Replies View Related

HIERARCHY HELP! PLEASE?

Feb 26, 2008

I have an Interesting situation that I'm hoping some of you experts can help me with basically I have the following hierarchy:

Net Profit
.....Gross Profit
..........Revenue
..........Direct Costs
.....Indirect Expense


Now, I have another hierarchy - separate from the above - that I need to assimilate to the hierarchy above - Example:

Expenses
.....Node a
..........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)
Net Sales Adjustments
.....Child a (Revenue type)
.....Child b (Revenue type)

Depending on the type, the top most node needs to be assigned to the appropriate node in the initial hierarcy, like this:

Net Profit
....Gross Profit
........Revenue
................Net Sales Adjustments
....................Child a (Revenue type)
....................Child b (Revenue type)
........Direct Costs
...........Expenses
.................Child b (Direct Cost type)
........Indirect Expense
............Expenses
.................Node a
.....................Child a (Indirect Expense Type)

Now, I've been able to figure out how to assign the top most node and leafs if all the children have the same type (using the expan stored proc listed in the books online), but my question is on this portion:

Expenses
.....Node a
...........Child a (Indirect Expense Type)
.....Child b (Direct Cost type)

Basically, the answer is to work backwards - if a child has a type that is different than the other children, a copy of the hierarch (up to the child) needs to be made and assigned to the appropriate initial node.

I've tried modifying the expand stored proc to give me the lineage of the child and see if there's a way I can copy the node and place it appropriately - I'm brainfried at this point, and I'm hoping that someone outthere can point me in the right direction.

Thanks in advance for your time

View 6 Replies View Related

Hierarchy

Mar 7, 2008

Hai everyone.,
i need to get solution for tree hierarchy in sql is there any solution or any keyword like 'connect by prior' in oracle ..
plz help me on this..

for example:
id | FName |parentid |
1 | sandy |
2 | robert| 1

if i give the parentid 1 in where condition of a query i need the details of 'sandy'

Thanks in Advance.
B.Arul.

View 2 Replies View Related

Hierarchy

Jul 10, 2006

Hello!I have a table that looks like this:Col1; Col2; Col3; Col4; Col538; 75; 233; 916; 277038; 75; 233; 916; 277138; 75; 233; 916; 277238; 75; 233; 923; 265438; 75; 233; 923; 265538; 75; 245; 913; 245438; 75; 245; 913; 2456....And I need a query (not a procedure) that shows me this:38; NULL; NULL; NULL; NULLNULL; 75; NULL; NULL; NULLNULL; NULL; 233; NULL; NULLNULL; NULL; NULL; 916; NULLNULL; NULL; NULL; NULL; 2770NULL; NULL; NULL; NULL; 2771NULL; NULL; NULL; NULL; 2772NULL; NULL; NULL; 923; NULLNULL; NULL; NULL; NULL; 2654NULL; NULL; NULL; NULL; 2655NULL; NULL; 245; NULL; NULLNULL; NULL; NULL; 913; NULLNULL; NULL; NULL; NULL; 2454NULL; NULL; NULL; NULL; 2456....Does anybody know how i can get this result? How?Help! Thank you!SQLNullps: SQL-Server 2000

View 3 Replies View Related

Hierarchy

May 24, 2006

Hi to All!

Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:

[Node | ParentId | ParentType | ChildId | ChildType]

But there is this nagging little voice saying it can be better

Cheers!

Nele

View 5 Replies View Related

Hierarchy ORBER BY

Mar 9, 2004

Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?

Thank you.


Table example

IDParentIDSortOrderCaption
1null1Main
211Files
313Folders
422File2
512Holders
621File1
752Holder2
851Holder1

Required result example

Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-Folders

View 3 Replies View Related

Hierarchy Level

Apr 28, 2008

Hi

i tried on CTE but iam not gettng correct result plz help me


Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0

Select * From @Tmaster

Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'


Select * From @TDetails

in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits

in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits


output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation

Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay

Others
Project allowance
Performance incentive
Medical insurance

View 12 Replies View Related

Ordering Within Hierarchy

Aug 7, 2007

Hi all,

Ive got a bit of a problem,

I have two tables:

CATEGORY
id
name
parent_CATEGORY_id

RECORD
id
CATEGORY_id
Stock_Held_Number

ps. Ive stripped out any non relevant fields


I also have the following query (again ive stripped out the non-relevant fields)

SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number

At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.

The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.

What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.

For example:

The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2

When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60

It's technically correct because I don't want the parent to calculate the total value of the children

What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get

Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40

Does anybody have any pointers as to how I can achieve this.

Many thanks

Mike

View 3 Replies View Related

Parameter Hierarchy

Feb 21, 2008

how do i create a parameter in hierarchy?

SAMPLE

billing period:
YEAR 2007
1st quarter
jan
feb
mar
2nd quarter
april
may
june


something like that.... if the user chose the 1st quarter the report will generate a sum-up report for the 1st quarter of 2007...


kindly reply asap.. i really need to get this report working..

thanks!

View 5 Replies View Related

Update Hierarchy

Nov 2, 2007

I have structure:FolderId, FolderName, ParentFolderId, FullPathe.g.1, First, null, First2, Sec, 1, First/Sec.....When update happens to FolderName, i need to update all FullPaths thatare below Folder which is changing.Any ideas?Thank you.

View 3 Replies View Related

Sparse Hierarchy T-SQL Help...PLEASE?

Jul 20, 2005

Greetings!I could really use some suggestions on how to improve on thefollowing, it at all possible:Table 'Customer'---------------------ID GUID PK....Table 'Facility'-----------------ID GUID PKCustomerID GUID FK (FK to Customer GUID)....Table 'Rate'----------------ID PKOwnerID GUID Nullable FK (FK to Customer, Facility GUID PK)OwnerLevel INT Contraint 1-3<Rate Data>Table 'Rate' is a sparse hierarchy of data. There are 3 possiblelevels in the hierarchy as follows:OwnerID <NULL>OwnerLevel 1This indicates Global rate data.OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data.OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data.Now, a given Customer need not have an entry in the Rate table. If aCustomer does not have an entry, it is supposed to 'inherit' Globalrate data. A given Facility need not have an entry in the Rate table.If a Facility does not have an entry, it is supposed to inheritCustomer-specific rate data, and in the absence of an entry for theFacility's parent Customer, it is supposed to inherit Global ratedata.The challenge is that I want to write a view to give me back theappropriate rate record for Customer and Facility. Here's what I'vedone so far.View _Rate--------------SELECTRate.*,NULL AS TargetIDFROMRateWHERERate.OwnerID IS NULLUNIONSELECTRate.*,Customer.ID AS TargetIDFROMRateCROSS JOINCustomerWHERERate.OwnerID IS NULLOR Rate.OwnerID = Customer.IDUNIONSELECTRate.*,Facility.ID AS TargetIDFROMRateCROSS JOINFacilityWHERERate.OwnerID IS NULLOR Rate.OwnerID IN (Facility.CustomerID, Facility.ID)View view_Rate--------------------SELECT_Rate.*FROM_RateINNER JOIN(SELECTTargetID,MAX(OwnerLevel) AS OwnerLevelFROM_RateGROUP BYTargetID) AS Filtered_RateON_Rate.TargetID = Filtered_Rate.TargetIDAND _Rate.OwnerLevel = Filtered_Rate.OwnerLevelThe combination of these two views gives a resultset that contains 1record for every Target ID as follows:TargetID <NULL>OwnerID <NULL>OwnerLevel 1This indicates Global rate data established at the Global level.TargetID <Customer.ID>OwnerID <NULL>OwnerLevel 1This indicates Customer rate data for the specific Customer identifiedby Customer.ID is inherited from the Global rate data.TargetID <Customer.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Customer-specific rate data for the specific Customeridentified by Customer.ID (not inherited).TargetID <Facility.ID>OwnerID <NULL>OwnerLevel 3This indicates Facility rate data is inherited from the Global ratedata.TargetID <Facility.ID>OwnerID <Customer.ID>OwnerLevel 2This indicates Facility rate data for the specific Facility identifiedby Facility.ID is inherited from the Facility's parent Customer'sCustomer-specific rate data.TargetID <Facility.ID>OwnerID <Facility.ID>OwnerLevel 3This indicates Facility-specific rate data for the specific Facilityidentified by Facility.ID (not inherited).I know this is a lengthy post, and a complicted query scenario, butI'm not willing to accept that my solution is the best solution justyet. Please consider that I really need this functionality in a VIEWas much as possible.Thank you for your learned consideration.I eagerly await your replies.Darryll

View 2 Replies View Related

Graph Hierarchy: What Is It Exactly?!!

Dec 21, 2007

Hi everybody,
I've been reading the book MCITP Self-Paced Training Kit (Exam 70-441): Designing Database Solutions by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits) as a part of my preparation for exam 70-441
The book is really clear, and I could understand everything easily because I've some experience as a database developer.
Until now, there's only one thing that I didn't fully understand:
On pages 53 and 54, it mentions a type of hierarchy called: a graph hierarchy, and it gives a small example made the things even more complicated
I've many books about database design theories, but they don't use the term (graph hierarchy) any where...
So, I appreciate if you can point me to an online resource or a book that discusses this term thorougly.

Thank you

View 3 Replies View Related

Concept Hierarchy-

Jul 13, 2006

What are some strategies in categorizing items?

View 4 Replies View Related

Use Hierarchy From SSAS

Sep 6, 2007

Hello experts,

Last time I worked with SSAS and build a Cube. Because I€™m now very happy with the front-end excel 2003 or excel 2007 I thought I build my own Report with SSRS.
Now there is something I don€™t understand:
I build a Hierarchy in SSAS that a want to use in SSRS. Is there a chance to use it without any features? Have I to use parameters or something another?
In my opinion it makes any sense to build a hierarchy new, because it€™s already exists in SSAS.

Have a nice day
Alex

View 5 Replies View Related

Hierarchy In Sql Server

Dec 5, 2007

Hello ,

I'm facing a complicated problem and I don't think that the solution will be an easy one.

I have an SQL statement in Oracle which I need to translate it in Sql Server 2005.

select lpad(' ',5*(orderid)) || to_char(descr) as menui
from <table _name>
where MENU_ITEM not in ('test1','test2','test3') and item_parent not in ('test4,'test5,'test6')
start with <item_parent='item_parent' >
connect by prior <menu_item = item_parent and menu_name='something'; > ?(condition)

Somewhere I have read that SQL server does not support Hierarchical Sql statements. Is this true ? How am I going to do that ?

Any help will be appreciated.

Thank you

View 7 Replies View Related

How To Get The Data In A Hierarchy?

Dec 12, 2007


How can I get the data from a table that stores hierarchical information keeping the right order and hierarchy levels?

I also need to specify a first level....let me explain.

Table: Sites
Id (PK)
Title
Url
ParentId


If I have something like this:

1 | Test | http://localhost | NULL
2 | Sub-Test | http://localhost/subtest | 1
3 | SecondItem | http://anotherServer | NULL
4 | ChildOfSub-Test | http://localhost/subtest/child | 2


And I specify that I want all child items below the Id 2, it must return:
Id | Title | Url | ParentId | Depth
2 | Sub-Test | http://localhost/subtest | 1 | 0

4 | ChildOfSub-Test | http://localhost/subtest/child | 2 | 1

How can I do this? I've saw some examples with CTEs, but they always return all of the items.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved