Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hierarchy repeating levels Expand / Collapse
Author
Message
Posted Friday, July 5, 2013 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 2:18 PM
Points: 1, Visits: 8
I am trying to create a very simple geographical hierarchy on a Carrier dimension. My data is something like this:

Carrier City State Country
--------------------------_---------
Carrier A | Chicago | IL | USA
Carrier B | Aurora | IL | USA
Carrier C | Stockton | CA | USA
Carrier D | Winnipeg | MB | Canada
Carrier E | Richmond | BC | Canada
Carrier F | Valparaiso| ZAC| Mexico
Carrier G | Veracruz | VER |Mexioc

My Hierarchy is defined as

Country
State
City
Carrier

Attribute Relationships:

Carrier --> City Name --> State Name --> Country

I expected the browser to display the hierarchy as follows:

USA
----------> IL
---------------> Chicago
--------------------> Carrier A
---------------> Aurora
--------------------> Carrier B
----------> CA
---------------> Stockton
--------------------> Carrier C
Canada
----------> MB
---------------> Winnipeg
--------------------> Carrier D
----------> BC
---------------> Richmond
--------------------> Carrier E
Mexico
----------> ZAC
---------------> Valparaiso
--------------------> Carrier F
----------> VER
---------------> Veracruz
--------------------> Carrier G

Instead it's repeating each level in the hierarchy:


USA
----------> IL
---------------> Chicago
--------------------> Carrier A
USA
----------> IL
---------------> Aurora
--------------------> Carrier B

USA
----------> CA
---------------> Stockton
--------------------> Carrier C
Canada
----------> MB
---------------> Winnipeg
--------------------> Carrier D
Canada

----------> BC
---------------> Richmond
--------------------> Carrier E
Mexico
----------> ZAC
---------------> Valparaiso
--------------------> Carrier F
Mexico
----------> VER
---------------> Veracruz
--------------------> Carrier G


Any idea why each level is repeated / not unique in the browser?
Post #1470868
Posted Monday, July 8, 2013 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:05 PM
Points: 13,327, Visits: 12,820
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse