Post a cube with two fields of measurement

[aleferrer]

Hello all,

I created a cube through a table from the database. This cube will output to return the information to the database.
When the table has only one field as I can post normally, but when the table has more than two fields of measurement, time to specify the variables that the cube can not recognize that I have two fields of measurement. Has anyone experienced this?
Annex demonstration of the fields in the table, VR_MARGEM_DISTRIBUIDORA_GNV, VR_PRECO_COMPRA, VR_PRECO_VENDA measurement fields of the table.

[Alan Kirk]

Hello all,

I created a cube through a table from the database. This cube will output to return the information to the database.
When the table has only one field as I can post normally, but when the table has more than two fields of measurement, time to specify the variables that the cube can not recognize that I have two fields of measurement. Has anyone experienced this?
Annex demonstration of the fields in the table, VR_MARGEM_DISTRIBUIDORA_GNV, VR_PRECO_COMPRA, VR_PRECO_VENDA measurement fields of the table.

It is at this point that you dump the cruddy inflexible code that the wizard generates for you and write your own.

Change all of your fields of measure to contents of "Other". In the Data tab you can then write CellPutN statements to write to as many measurement elements as you want.

I wouldn't say that you should never use wizard generated code but... oh wait, yeah I would. Never use wizard generated code.

I've never found the wizard itself to be that intuitive but more to the point you have no room to move; you get what you get. The time wasted in trying to learn what the wizard will do ("As Is? As Is What?") could be better invested in learning TI code itself so that you can take control of the process and don't have to figure out the right way of asking the wizard to do what you want.

You can take a look at what the wizard is doing to load the single field to get you started, and replace that code as you need to OUTSIDE the generated statements. In doing so you'll find that you have total control over what the code does and when it does it and can modify it any way you like.

[Duncan P]

For people who don't code, or won't code, then the wizard is worth understanding and using. It is actually very capable. There are few structural requirements that it can't cope with. The only caveat is that it creates stuff effectively but doesn't necessarily change existing stuff so reliably. Granted, you (Alan) may not like the code it generates but if instead you're one of those that doesn't code you're not going to know the difference.

@OP could you explain in a bit more detail what exact problem you are having when there are multiple measure columns in your data source. The first two pictures that you posted seem to indicate that you have set it up OK. What is actually happening?

[Alan Kirk]

For people who don't code, or won't code, then

then... they should probably find an alternative vocation since administering a data system (well) necessarily involves some understanding of how the data moves through that system. Failure to understand that will usually at some point or other result in a metaphorical train wreck of numbers which were not processed in the way that they were expected to be processed. If their excuse was "But I didn't understand what the code was doing!" then I'm not sure that their boss would need to be Donald Trump for them to be hearing those two magical words.

Of course this problem can be avoided by them understanding the code that the wizard generates, in which case they're covered.

But then, if they can understand the code that the wizard generates, then they may as well write it themselves and not be constrained by the limits of the wizard.

This does not fit with the IBM mindset of "wrap everything in slow moving GUIness until the user can barely move in any direction other than the one that it was assumed that they would in the development studio" (anything written in Java and released in 10.1, I'm looking at you with simmering malevolence), but it still reflects one of the core objections that I have to the wizard.

the wizard is worth understanding

Understanding indeed. Another of my objections is that the wizard is not, IMHO, particularly user friendly and you have to put in a reasonable amount of effort into understanding what each of the dialogs, drop downs and options is supposed to be doing, what it does do, and what it (naturally) can't do so that you aren't buried in endless "nuh-uh" dialogs. Contrary to conventional wisdom the mere fact that something is "graphical" does not automatically make it "intuitive".

It therefore takes an investment of time to understand how it works. And if time is going to be invested in learning something anyway, would it be better used:
(a) To understand how to do whatever you want to within the limits of the tool; or
(b) Merely to understand how to make the user interface to the tool get you to where it tells you you can get to?

My feeling is that (a) is the preferable option. It's not like TI is a particularly difficult language to learn; the expression "feature rich" does not come to mind.

Array variables?
Nope, just some kludgy if ingenious workarounds.
Loops?
One, count them, one kind (and a non-auto-incrementing one at that; find me the person who has never once forgotten to increment the counter).
Ability to regulate chore scheduling within TI?
Nope.
Built in e-mailing capability?
Hmmm...
Etc, etc...

It's a (very) basic scripting language, and users who are in an administrative role at least (who are generally not slack jawed idiots who can do nothing more than point and click and swoosh on pretty icons) don't need to be Alan Turing to be able to figure it out.

and using. It is actually very capable. There are few structural requirements that it can't cope with. The only caveat is that it creates stuff effectively but doesn't necessarily change existing stuff so reliably. Granted, you (Alan) may not like the code it generates but if instead you're one of those that doesn't code you're not going to know the difference.

I have less of an objection to the code as such, more of an objection to the limitations of the code that CAN be generated. (And, as noted above, the time that it takes to get to that code via a GUI click-fest in the first place.) TI is supposed to be an ETL tool and being limited to the wizard places limits on how much "T" can be done there.

But speaking of the code, I do agree that more details about the original problem would be useful and it would also be useful to see the code that has been generated on the Advanced tabs so that we can see exactly what the Wizard is trying to do and how it does or doesn't match your expectations.

[aleferrer]

Duncan P

My problem is to generate the cube. I am creating a cube through a table from the database. When my table has only one field measurement, for example, a table with 4 fields and one of this measure, to create the cube, how do I set up three columns and a string as data variables tab, tab TM1 dimensions takes on the dimension measurement implicitly create this cube and then make his publication in the database, with the same table structure. When the table has more than two fields of action, variables tab I set all fields of measures and dimensions in the TM1 guide asks me to create a measures dimension to my cube when I'll publish this cube on the table, the database means that the field names as the size of the cube are string, preventing me from publishing.
Note the image of the cube, the fields are not measures of dimensions rather than one dimension items, my problem is this. I need fields'' are'' measures as dimensions so I can publciar the cube in the database.

Thanks

[Alan Kirk]

My problem is to generate the cube.

Your problem is, to put it as delicately as possible, difficult to fathom.

If you have a data source with multiple data columns it will look something like this:

Multifielddatafile.JPG (68.62 KiB) Viewed 8254 times

If you DO insist on using the wizard the setup will look something like this:

Wizard.JPG (39.27 KiB) Viewed 8254 times

Each of the "Element" columns represents one element in one dimension. To put that another way, each column relates to one distinct dimension. Each row represents one element in the corresponding dimension.

But ALL of the "Data" columns relate to ONE single dimension. Each column is simply assigned to different ELEMENTS of that dimension.

Consequently your Maps tab /Dimensions sub-tab will look something like this, where every "Element" column has to specify the dimension that the element appears in. And note that "AsIs" only works if the dimensions /elements already exist. If you need to add any new elements as they come in through the data feed, change your setting accordingly. (Refer to the TI manual for an explanation of what each option does.)

Wizard2.JPG (44.35 KiB) Viewed 8254 times

All of your "Data" source columns are grouped into that last row. Note that you have to specify which dimension the values contained in those columns will be written to. This will be what I assume you're referring to as your "measures" dimension. I've called it zDimData, you can call it whatever you want; I can't read the name of the dimension that you have in the columns area of your last screenshot, but that would probably be it. To assign those columns to elements in that dimension you have to move to the Data sub-tab of the Maps tab.

Wizard3.JPG (24.8 KiB) Viewed 8254 times

In this case I've used different element names from the column names. In your case you will probably use the original column names from your data source as your element names. These will then become the individual elements of your "measures" dimension that you have running across the top of your screenshot.

[aleferrer]

Thank you, Alan.

My problem is this. Due to difficulties with the language, I'm not getting explain.

Duncan / Alan

Here attached file with the whole process. This in my native language, Portuguese.
If you can help me, I'll be very grateful.
What I need is to publish a cube. This cube has 6 data fields.
When the cube has only one data field I can.
Follow file with step by step to create the cube.
I'll attach another file with step by step to publish the cube.

thank you

[aleferrer]

Step by step to publish the cube.

[tomok]

What you are attempting to do is just basic TI process writing and is covered in detail in the manuals, as well as in most of the many training courses taught around the world. I'm sure you can find one in Brazil. Surely if your company was willing to invest in TM1 licenses they would also be willing to invest in some training for you.

[Duncan P]

Hi aleferrer,

Thanks for all the extra stuff. I will have a read over the weekend but just wanted to point you to a couple of posts that I think are relevant to the problems that you are having.

Your recent comments seem to indicate that the cube is being created fine but that you are having difficulty in publishing to a SQL database when there is more than one measure. This is quite a common problem and has been addressed in a couple of places before.

The fundamental problem is that when you use a TM1 cube as a TI datasource it gives you a record for each populated cell but what you want is a record for each row of populated measures so that you can turn it into a SQL insert statement.

Have a look in the following posts and see if they help.

• Asciioutput issue
• Export periods across columns subset for source view
• Can not retrieve cell value using CellGetN !
• Duplicates in TM1 Extract

Good luck,
Duncan.

[aleferrer]

That's it, Duncan!
Do you understand my problem.
I will analyze the posts.
If you can help with other possible solutions, thank you.

Thank you

[aleferrer]

Good morning, everyone!
I still have the problem of the post.
If you find the solution, please post.

thank you

[Duncan P]

I have a small example here of the technique. It uses an indicator measure to mark which rows have values in any of the measures and it outputs to file the SQL statements that it would have issued to the ODBC connection.

The key is in the definition of the view "indicator" which you must look at from within the process editor.

export.zip

Export example

(3.53 KiB) Downloaded 389 times

[strade]

Thank you Alan Kirk for your detailed explanation.
I had the same problem with creating a cube with multiple data columns.
Understanding that each measurment must belong to a single dimension element was the key.
After creating a dimension that discribes my data columns everything worked fine!

Greetings from Germany!