Error trying to Import dimension into Cube

[pgorge12]

Hello, I imported a package into Cube Designer from framework manager and I am attempting to import a regular dimension into a new cube. Although there are dimensions I am able to bring in, there is one that gives me this error (see attached jpeg). This Fiscal dimension has 3 levels: Year, Quarter, and month and they created based on calculations in Framework manager. My year level only has a business key and the year value is being extracted from a query item. The definition for that business key looks like this:

_year ( _add_months ( [Teknor Data Warehouse (Query)].[Fact_SalesTable].[Accounting Date] ,5))

It works in framework manager. Perhaps the error I am getting in Cube Designer (regarding an external column) is due to the query item 'Accounting Date' no being physically in the Fiscal regular dimension. Is this an issue with Cube Designer or is there something that must be done on Framework manager to resolve this issue? Does there have to be a business caption in the level in order for this to go away?

Thanks

[bus_pass_man]

i.
Here's some ideas about things to test:
1.
I can't say that I quite know what the message is trying to say but that's fairly common with Cognos stuff, unfortunately. At least the message is a grammatically correct sentence.
It might be possible that cube designer knows what is wrong but just doesn't know how to say it.  The key might be 'external'.  It might be that it can't determine what query items and what source columns to use when it unwinds the dimension and creates the relational model.

Do you have anything in the dimension which references something in the dimension?

I think you mention that the fiscal dimension is based on calculations.  Are there, ultimately, query items referenced in them?  Do you have macros defining the query items?

2.
A quick way to test the hypothesis that the missing member caption role is responsible would be to set that role, publish a package, import it into cube designer, and try to create the dimension.  If the problem doesn't happen then you've identified the source of the problem.  If it still does, you've narrowed down the solution space.

3.
Just to see if I understand what is happening, is that you have in your FM model a dimension in which you are using a column from the fact table in the dimension along with columns from your dimension table.  Is that a correct understanding?
I don't think that *should* matter.  Degenerate dimensions should work and a hybrid should too, although off the top of my head you might run into difficulties if you try to use your fiscal dimension in other cubes as the cardinality of the relationship between fact_salestable and your fiscal dimension table would be going against the normal pointy rule flow.

Just out of curiosity, can you explain why this design decision was made?

ii.
Here's a suggestion for proceeding without doing any troubleshooting:
Did the dimension get created but with errors? Can you edit the dimension in cube designer to have the expression that you want? If the dimension was not created, can you manually create the dimension and get the results you were expecting?

[pgorge12]

i.
1. They key was made in Framework and its expression is the one I mentioned above: _year ( _add_months ( [Teknor Data Warehouse (Query)].[Fact_SalesTable].[Accounting Date] ,5))

[Fact_SalesTable].[Accounting Date] is indeed a query item that is referenced in the business key of the dimension. My Quarter and Month keys are using calculations and not query items.

2. I made a business caption for Year to test my theory. It was an expression to turn the key into a string (because captions have to be strings) and I still received the same error regarding external column.

3. You would be correct. The dimension is using that column I mentioned above from a fact table as its key. The other levels are using calculations like I said. You make a valid point in that when I need to make other fact tables, this fiscal dimension will be confusing since I will be using different dates based on fact tables.

Also, I made measure dimensions in Framework and they were unable to be made in Cube Designer as well. These measure dimensions include fields from the fact table that have the usage of 'fact' in their properties on framework. They are all measures and I put them in measure dimensions. However, I still receive the 'referenced external column' error.

ii
The dimensions are not being created in Cube designer. I just receive that error. I manually created the fiscal dimension with a level and it did validate the expressions. I still got a couple issues but they were relationship-related. I would still prefer being able to import the dimensions as I created them in Framework.