0

I'm working on Cognos 11 on a new Data Module. I'm using fact tables and dimension tables.

One of those tables is Geography. For a location I have all the information someone could need to use.

My problem is as follow : I have a departure location and an arrival destination. For now, Conos 11 does not allow me to use my Geography table multiple times so it may describe Departures and Arrivals

Does someone know how I could solve my problem ?

Obligation : We do not want to upload a file, so extract/create a file/upload on C11 is not valid

Valentin C
  • 161
  • 1
  • 7
  • 2
    That's a pretty crappy limitation. Best I could suggest would be to get views created in your database, since you can't create aliases. – Andrew Jan 23 '19 at 15:40
  • You omitted critical information from your question. Assuming you are using a package, and the model was designed properly, you may be trying to solve a problem that doesn't exist. What is the data source for your data module (package, data module, data set, file, etc.)? Is this a package with multiple facts with Geography joined to each fact differently? Why do you need a data module with custom joins versus just using the package directly? – dougp Jan 23 '19 at 18:38
  • Like Andrew said, best will be to create a specific view for departures and specific view for Aarrivals. Both are in one unique fact table. Here Departures and Arrivals are airports and ports. One unique list in database unify both. – Valentin C Jan 30 '19 at 16:56

1 Answers1

0

Kimball et al say that generic dimensions isn't a good idea. The geographic attributes belong in the dimensions which these attributes describe. For example, have geographic attributes in your customer dimension, geographic attributes in your store dimension, and geographic attributes in your warehouse dimension etc.

If you upgrade to 11.1.1 there's copy and paste functionality, which will allow you to create aliases. You can also create views, unions, excepts etc.

C'est Moi
  • 326
  • 1
  • 2
  • 8
  • I'm not sure Geography qualifies as "generic dimension" here as it describes an actual thing, namely a place on Earth. A generic dimension would just be lists of possible values that aren't actually describing an entity or instance of something. – Johnsonium Jan 24 '19 at 17:49
  • No, you've missed the point altogether. Here's something from Kimball which makes a good, brief reading. https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/abstract-generic-dimension/ – C'est Moi Jan 25 '19 at 18:44
  • As always with database design -- it depends. Perhaps in the OP's business, like mine, isn't product sales. Maybe each row in the fact represents an event that can have its own geography, independent of the other dimensions. Geography would then be one descriptor (dimension) for the event. Although, in that case it is likely that geography would contain as many rows as the fact, leading one to consider making it a degenerate dimension. But we're off topic. This is a Cognos question, not a data warehouse design question. – dougp Jan 29 '19 at 16:42