TI Variables wrongly assigned in PAW

[Andero]

Hi,

I'm creating a TI process to copy data from one cube to another, but in the data section the variables are assigned with a different order.

For example, I have these two dimensions:

Products
- Product 1
- Product 2
- product 3

Warehouse
- Wh 1
- Wh 2
- Wh 3

But when I move to the data section, variable Products is containing Wh1, Wh2, and Warehouse contains Product 1, Product 2, ..

In the TI Data source I select the source cube, then I assign the variables that are the dimensions of the cube.
Everything here looks fine and the variable preview shows the correct assignment, so in the variable Products I can see Product 1 and in the variable Warehouse i can see Wh1

I usually set a random view in the data source, then I build the correct view (of the same cube) in the prolog, I created hundreds of TIs this way in architect and never had this problem...

I just noticed that this problem happens only when the starting view is created in PAW, if the view is created in architect is ok

How is it possible?

Thanks in advance

[MarenC]

Hi,

Not sure about your issue, I do know that there are some posts on here about MDX views creating variable issues when used as a source in a TI.
If this is a bug then it is a concerning one, no doubt about that.

I will be honest, I still use Architect for stuff like this.
I tend to build the view in the Prolog too, but I build the view in the prolog and run the process, to build the view.
I then link this created view in the datasource section of the process.
Then I set the view and subsets in the prolog to be temporary, set the datasourcename etc and carry on finishing the process, i.e. updating the meta and data tabs.
I finally make sure the view and subsets created by the process initially are deleted.

If you take this approach (but in PAW instead of architect) does that solve the issue?

Maren

[Andero]

Hi,

this is what I usually do:

in data Source: I select a random view of the cube that I want to take the data from
in prolog: I build a temporary view + temp subsets and replace the old view with the global variable DatasourceCubeview
in metadata/data: now I have the right set of data to complete my process

What I discovered is:
- if the random view I chose in the data source has been created in PAW, the variables in the data section are messed up
- if the view was created in architect, then no issue.

From now on, i'll make sure to build all my views in architect.

I was just wondering if this is a known problem or maybe i was doing something wrong

Andrea

[MarenC]

Hi,

You already told us all that in your first comment.

The key is how did you build the random view?

What I am saying, try using the view you create in the prolog as the datasource of your view, instead of the random view.
Set the variables, and then update the rest of the process, i.e. meta and data etc etc

Maren

[Andero]

Hi Maren,

The "random" view, is a standard view that I create when I build the cube, it's a general view.

But often the TI requires a smaller portion of the cube, i keep the leaves only and more importantly there are parameters (for example the year), this is why i define a new view in the prolog

I cannot set the new view in the data source, because it's a temporary view generated in the prolog, sorry but i don't understand what you mean with
" try using the view you create in the prolog as the datasource of your view, instead of the random view." because it's generated after.

In any case, this is only a problem related to paw, never had this kind of issues when the starting view comes from architect

Andrea

[glaurens]

Hi,

I think the easiest way to do this is to firstly not create the view as temporary. Then use that as reference for your process, setup the variables and then delete the non-temporary view afterwards.

Obviously, then set the view back to Temporary.

G

[glaurens]

Goed gedaan, Wim

[MarenC]

Hi,

as I said in my first reply, you create the view in the process, but don't make it a temporary view, You run the process to create the view, then go to the data source section and set this view as your source.

Then update your code to make the view temporary and update the meta and data tabs as required. It is all there in my first comment.

Maren

[lotsaram]

The problem will be that you're creating the view in Workspace. All views in vorkspace are per definition MDX views. MDX views assign index order of variables based on the view structure. 1st column dimensions (MDX axis 0), then row dimensions (MDX axis 1), then slicer dimensions. So if you constructed your view with all dimensions on columns in the display order of the cube then this would be the easiest way to solve the problem. Assuming the temp view which gets created with TI is a trad view. Still seems rather fiddly to me.

Or you could not use a view at all for your initial datasource for editing, just use a CSV file! And then on the prolog where you create the temp view make sure you also declare DatasourceType = 'VIEW' and DatasourceNameForServer = sSourceCube in addition to setting DatasourceCubeView which you must be doing already. For my 2c this would be the easier and my preferred option.

But then again I would just use bedrock and not write a custom process each time I wanted to transfer data within or between cubes.

Note (also with reference to the IBM technote above) that if you build the source view with MDX as opposed to a trad view then you really have no option except to match the variable order to how the query has been constructed as the variable order for a MDX view will always follow the query structure and not the cube dimension order. Also note that for an MDX view the number of variables not including nValue, sValue and value_is_string, may be more (or less) than the number of dimension in the cube depending on how the query is structured (e.g. omitting dimensions from WHERE and rely on default member = less variables, intersecting multiple hierarchies of a dimension = more variables). This makes using MDX views more or less impossible to work with when it comes to designing generic TI processes.