MS SQL Server :: Displaying Data In Hierarchy From Single Table..

Analysis :: Displaying Two Levels From Same Hierarchy In Two Columns

Oct 7, 2015

I want to display Month and year from same time hierarchy in two separate columns.My hierarchy is as below:

Ship Date > Ship Date Hierarchy
Ship Date year
Ship Date Quarter
Ship Date month
The display I am looking for is as below
Ship Date year Ship Date Month
measures
2015 Jan
200
2015 Feb
300

I am using Tail function as I need only the last 13 months, below is my query:

with set [Month] as
{
tail([SHIP DATE].[Ship Date Hierarchy].[SHIP CAL MTH].members, 13)
}
select
{[Measures].[TAG BUID Distinct Count]} on 0,
{[Month]* [SHIP DATE].[Ship Date Hierarchy].[SHIP CAL YEAR]} on 1
from
[GSDR_P4]

This gives me error that The Ship Date Hierarchy hierarchy is used more than once in the Crossjoin function.

View 2 Replies View Related

Return The Data From A Table Ordered By Its Hierarchy

May 3, 2006

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.

Thanks.

View 9 Replies View Related

Build Hierarchy - Store Data Link In Table

Sep 3, 2013

I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this

Id-Hreid

Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get

Emp3-Emp2
Emp3-Emp1
Emp2-Emp1

View 2 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date

Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000

Should Appear

Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Displaying Data In The Table Of Report On Text Box

May 23, 2008

Hi,
In my report, I have a table that displays all the data from query. I need to take the value of the 3rd row,2nd column of the table and display in a text box.
I tried to do like:

=ReportItems("TextBox2").Value., where TextBox2 is the 3rd row,2nd column of the table.
This returns just the first row value of the seleted data. But, I need the 3rd row value.

Please let me know how can I acheive this.

View 1 Replies View Related

Analysis :: Include And Exclude Children In A Single Hierarchy In Parent Child Dimension In MDX

Apr 8, 2015

Include children and exclude children in a single hierarchy in parent child dimension in mdx

*12-parent
**20-parent
- 9-parent
--250-child1
--210-child2
--240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children

View 3 Replies View Related

Displaying Multiple Charts Using A Single Dataset

Jun 15, 2007

Hello,

I have the following tables :

Area
areano
areaname

Locality

locno
areano

RequestType
reqid
reqdtls

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.

I have written the query for this.

select q.[areaname] as Area,

classCount as TotalCount, ReqType

from ( select a.areano,

a.classCount, ReqType

from ( select areano,

count(*) as classCount, reqdtls as ReqType

from WebSummit

join RequestType

on WebSummit.reqid = RequestType.reqid

where date_created >= dateadd(mm, -3, getdate())

group by areano, reqdtls

) a

join WebSummit b

on a.areano = b.areano

group by a.areano,

a.classCount, ReqType

) p

join Area q

on p.areano = q.areano

The query works fine and has been tested. Now I want to display this in chart format.

The display should be in a chart format, one for each area showing a bar for each RequestType.

I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.

I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.

Plz help me out.

Here's some sample data

Area

AreaNo AreaName

1 Area1

2 Area2

3 Area 3

4 Area4

5 Area5

6 Area6

7 Area7

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 1

4 Ben 12/6/2007 3 1

5 Dale 14/6/2007 1 2

6 Evjen 15/6/2007 1 2

7 Fuller 16/6/2007 2 2

8 Jimmy 16/6/2007 3 2

9 Kart 16/6/2007 3 2

10 Fuller 16/6/2007 3 2

Regards,

Vidya

View 1 Replies View Related

Insert Data Into A Table From Two Tables Into A Single Table Along With A Hard Coded Value?

Feb 9, 2012

I'm trying to insert data into a table from two tables into a single table along with a hard coded value.

insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)

I get the following-

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

Do I need to use a cursor?

View 5 Replies View Related

SQL Server 2012 :: Displaying Code Of A Stored Procedure To A Single Line

Sep 14, 2015

Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.

I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.

Select col1.[type],col1.[name],convert(varchar(max),col1.text) + isnull(convert(varchar(max),col2.Text),'')
+ isnull(convert(varchar(max),col3.Text),'')
+ isnull(convert(varchar(max),col4.Text),'')
+ isnull(convert(varchar(max),col5.Text),'')
+ isnull(convert(varchar(max),col6.Text),'')
+ isnull(convert(varchar(max),col7.Text),'')

[Code] .....

View 3 Replies View Related

Retrieving Hierarchical Data From A Single Table

Sep 3, 2006

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains

View 4 Replies View Related

Help With Creating SQL Statement To Get Data From Single Table...

Aug 4, 2005

Hi, I'm having some difficulty creating the SQL Statement for getting some data from a table:
I have the following table of data
__User___Votes___Month
__A_______14______2__A_______12______3__A_______17______4__A_______11______5
__B_______19______2__B_______12______3__B_______15______4

I want to beable to pull out the total number of votes a user has had over a period of months.
eg Total up each users users votes for months 4 and 5
that would give:
__User____TotalVotes
___A________28___B________15
An added complecation is that user B does not have any data for month 5
Any help or pointers would be fanstatic
Many thanks

View 3 Replies View Related

Analysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?

Aug 11, 2015

I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.

Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.

View 2 Replies View Related

Insert Data From Multiple Sources To A Single Table

Sep 24, 2015

I am have a situation to insert data from multiple sources to a single table.

i.e., multiple and concurrent insert on same table

Will it lead to dead lock at any point? is there any possibility?

How insert will work ? What is the architecture ? Any references to read?

View 1 Replies View Related

Comparing Data In Two Consecutive Rows From A Single Table

Jul 20, 2005

I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.

View 22 Replies View Related

T-SQL (SS2K8) :: Compare Data In A Single Table By Month Period?

May 28, 2014

i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it

select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'

i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06

select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)

View 4 Replies View Related

Loading The Different Language Data From Excel File To The Single Table

Feb 29, 2008

can anyone help me to solve this problem
i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english.
so please tell me how to solve that
reply to my mail id(sandeep_shetty@mindtree.com)

View 11 Replies View Related

Data Flow Task - Multiple Columns From Different Sources To A Single Table

Dec 19, 2006

Hi:

I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks

MA2005

View 9 Replies View Related

How To Get The Data In A Hierarchy?

Dec 12, 2007

View 7 Replies View Related

Create Table Hierarchy??

Oct 26, 2007

Hi, with the three tables below, I want to add relationships so that each Grandparent can spawn many parents and each Parent can spawn many children.

I keep getting an error when I try to connect them. Could I get some help creating that hierarchy please?

Thanks in advance!

Here are the tables:

CREATE TABLE [dbo].[GrandParent] (
[GPID] [int] IDENTITY (1, 1) NOT NULL ,
[GPName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Parent] (
[PID] [int] NOT NULL ,
[GPID] [int] NOT NULL ,
[PName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child] (
[CID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[ChName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

--PhB

View 7 Replies View Related

Fetch Data From Hierarchy In XML.

Oct 11, 2007

My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.

------XML-file---------------------------------------
<my:group1>
<my:group2>
<my:hustypTF>5</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>6</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>7</my:hustypTF>
</my:group2>
</my:group1>
-----------------------------------------------------

------T-SQL for fetching data from XML datatype------
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet
FROM MinaDekl
CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1)
-----------------

View 2 Replies View Related

Trying To Enforce Ref Integrity On Hierarchy Table Relationship

Dec 28, 2007

I've got a table that includes:CREATE TABLE [dbo].[Content] ( [Id] int IDENTITY(1, 1) NOT NULL, [ParentId] int NULL,
I'm wanting to make sure that a ParentId must be in the table as Id someplace else. When I try to do it by making it a foreign key get the error:
--------------- SQL ---------------
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
---------- ERROR MESSAGE ----------
There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors.

Any ideas?

ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [Content].[dbo] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO

View 3 Replies View Related

SQL Server 2012 :: Better Way To Query A Hierarchy Table?

Jan 21, 2015

I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.

My question: is there a better way to query that hierarchy table instead of using recursive?

** The current user-defined function that is written using recursive:

CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)

[code].....

** And definition of table 'DepartmentItem' :

DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: Clone Hierarchy To Another Table

Apr 4, 2015

I have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.

CREATE TABLE [dbo].[t_prod_cat](
[cat_node_id] [bigint] IDENTITY(1,1) NOT NULL,
[advertiser_id] [bigint] NOT NULL,
[cat_hid] [hierarchyid] NULL,
[level] AS ([cat_hid].[GetLevel]()) PERSISTED,
CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED
(
[cat_node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.

Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"

CREATE TABLE [dbo].[t_prod_cat_work](
[temp_cat_node_id] [bigint] NOT NULL,
[temp_cat_hid] [hierarchyid] NOT NULL,
[advertiser_id] [bigint] NOT NULL
) ON [PRIMARY]

What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?

View 2 Replies View Related

Using Hierarchy ID Data Type In Material Database

Nov 10, 2014

How to use hierarchy id data type in Bill of material database.

The msdn example [URL] ....

View 1 Replies View Related

Aggregation In A Hierarchy Involving Non-leaf Data.

May 22, 2008

NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.

The Question:

The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.

When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number

I say (A) and I think most will choose the same, and all the examples I've been reflects (A).

However, in my simple cube I get (B).

Is there a setting I need to adjust to get (A)?

View 16 Replies View Related

How To Find Orphaned Value From Parent / Child Hierarchy Table

Aug 15, 2013

how to find the orphaned value from the below parent/child hierarchy Table.

create table dbo.Hier(parent varchar(100), child varchar(100))

insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'

Delete from Hier where child='uma'

I tried:

select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma

I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?

View 2 Replies View Related

Retrieve Manager/Subordinate Hierarchy From Self-Referencing Table

Jul 23, 2005

Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5

View 3 Replies View Related

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

SQL 2012 :: Load Consolidated Members Into MDS Staging Table - Hierarchy Name

May 28, 2015

I'm attempting to load some data into an explicit hierarchy in MDS 2012 via the staging table and struggling with the HierarchyName field. Specifically I'm loading data into stg.[Entity Name]_Consolidated and using the exact name of the explicit hierarchy I've set up in the front end web application.

Originally my hierarchy was labelled "Reporting Hierarchy" and when loading the data into staging using this name then running the batch from the Import Data screen I can see the error message "Error - The HierarchyName is missing or is not valid.". I've checked the table mdm.tblHierarchy and can see that the name there is exactly as it was in the staging table and have since renamed the hierarchy as "Reporting_Hierarchy" with the same results.

View 0 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

Analysis :: SSAS LastnonEmpty Not Showing Data For Entire Time Hierarchy?

Jun 8, 2015

I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.

I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.

what I am doing wrong.

View 8 Replies View Related

SQL Server 2012 :: Query To Generate Relationship (Parent Child Hierarchy From A Table)

Jul 18, 2015

I am working on a query to generate parent child hierarchy from a table.

Table has below records.

--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable

[Code] ...

how to achieve this.l tried with temp tables it doesn't work.

View 5 Replies View Related