Nested Dimension?

[thomassch]

Hi all,

most times I'm facing a problem I am able to find a solution here in the communitiy.
But for the recent challenge I'm some kind of stuck; hopefully one of you has an idea how to solve it.
See attached file for a simplified example:
I have a time-dimension (here numerous week, but that's not the problem) and a dimension with three 'levels';
what I want to do is to show sums of different levels (according to the users selection) over time
The selections in level 3 (city) shall always be displayed as one total sum, the selections in level 2 each as their own sum
Additionaly the total sum should be displayed as well (if any selection regarding to this continent is made)

The only way I was able to handle it is to build an own (conditioned) 'fact' for each and every line, but as in my original data there are several hundreds of cities that's not really a solution
Probably I have to build some kind of nested dimension, but I have no idea how to do it?

[jwaligora]

Hi,

Not sure I will have an answer, but I was just hoping to clarify the usage/format. 

There may be simple solutions if the portion of data exposed at any time isn't large. Is there any limit on the number of Continent/Country/City combinations that will be displayed at any given time (e.g. only 1 city can be selected)? How many weeks of data are you loading (one month's worth or one year's worth)?

A pivot can show the data nicely, in a tiered manner, but I'm guessing this is not the display format you're after:

Thanks,
J.

P.S. In case anyone else wants to play, here's a loadable sample data set:

Load * inline 
	[Continent,Country,City,TheWeek,Amount,Price,Totals
	Europe,Germany,Hamburg,1,100,5,500
	Europe,Germany,Cologne,1,120,6,720
	Europe,Great Britain,London,1,100,7,700
	Europe,Great Britain,Liverpool,1,160,7,1120
	Europe,Italy,Rome,1,110,6,660
	Europe,Italy,Venedig,1,90,5,450
	Europe,Italy,Firenze,1,90,5,450
	Europe,Greece,Athens,1,30,8,240
	Asia,Japan,Tokio,1,50,8,400
	Europe,Germany,Hamburg,2,85,7,595
	Europe,Germany,Cologne,2,110,6,660
	Europe,Great Britain,London,2,120,5,600
	Europe,Great Britain,Liverpool,2,150,6,900
	Europe,Italy,Rome,2,150,7,1050
	Europe,Italy,Venedig,2,80,8,640
	Europe,Italy,Firenze,2,70,6,420
	Europe,Greece,Athens,2,25,7,175
	Asia,Japan,Tokio,2,90,7,630]
;

[thomassch]

Hi jwaligora,

thanks for your response.

Unfortunately there isn't supposed to be a limit to the displayed continents/countries/cities, there might be dozens or hundreds. But if you have an idea of how to deal with it if only few values are selected, that might help as well.

Just to clarify, it is ensured that each city belongs to exactly one country, and each country belongs to exactly one continent.
The calender-dimension is a cyclic group containing years, months, weeks and days, so there might be the necessarity to show 365 days.

The graph-type is quite fixed, it has to be a line or bar chart.

Regards