Create an import TI manually

[jviegas@bi4all.pt]

Hi,

I've been searching for a topic regarding this but since I haven't been successful I decided to ask for some help here.

I have some TI processes running but I need to change them because of changes on the password access to the enviroments that I'm using as source.

I've created a cube to maintain the user and password for each dimension (I will apply security after), and I'm thinking on doing the odbc in the prolog:

DatasourceUsername=CellGetS(user in admin cube);
DatasourcePassword=CellGetS(password in admin cube);
ODBCOpen('ODBC Source',DatasourceUsername,DatasourcePassword);
DatasourceQuery = Expand ( 'Select * .......... from ........');
ODBCOutput('ODBC Source',DatasourceQuery );

My doubts are :
- How to map the variables (as in a TI using the datasource wizard;
- How to replicate the behavior of metadata and/or data that read through all records of the data source to either update dimensions or import data to a cube.

Can any one help me on this is (even by sending a helpful url or other post that maybe I haven't find it) ? thanks

[jim wood]

I'm really not sure what you're trying to do? If you are looking to override the source query you're going about it in completly the wrong way. You need to use data override. Thne your variables have to match the original variables delivered by the original query. If you search data source override you'll fin plenty of topics on it like:

http://www.tm1forum.com/viewtopic.php?f=3&t=6020

This covers using a view as the data source but you'll get the idea,

Jim.

[jviegas@bi4all.pt]

Thanks Jim,

My situation is that I need to use some Oracle tables as sources in diferent processes (some to load dimensions, other do load data to cubes).

Because of security reasons I will have a cube to put the user and password for the odbc that only the admin group will have access.

I'm thinking on creating the datasource by code but when doing this I only have access to the epilog and prolog tab. My doubt is how to make the process run to all the records of the query in the data source and do the actions I want (either as metadata tab or data tab depending on the process).

Kind Regards,

Jorge

[rmackenzie]

My doubts are :
- How to map the variables (as in a TI using the datasource wizard;

TI isn't really sophisticated enough to re-map variables at runtime in a wizardy sort of way - for example, if your 'template' TI has 4 variables but you switch the DataSourceQuery to a table with 8 columns then there's little you can do to make the process recognise the extra 4 variables. Your option is to make the 'template' TI have the maximum number of variables you expect to find from the record-set of a query but then you may well have to write a lot of god-awful IF-based logic to decide how to map you dummy variable to a real field.

My doubts are :...
- How to replicate the behavior of metadata and/or data that read through all records of the data source to either update dimensions or import data to a cube.

It sounds like you are attempting to create the 'template' TI to work for any dimension building/ updating process. To get this sort of pattern up and running you need to work with the database you are using as a source and create a 'template' view on any table used as an input for the dimension update process. For example, if all dimensions just need 4 columns which are DimName, ParentName, ChildName, Weight then you can set-up a single TI to have 4 variables and then make sure that the SQL you pass to DataSourceQuery only will ever have these 4 columns in it's record set.

Your code then becomes a simple matter of:

Code: Select all

# sql using pDimName as parameter
SELECT DimName, ParentName, ChildName, Weight FROM '?SOURCE_TABLE?'
WHERE DimName = '?pDimName?'

# Metadata
DimensionElementInsert ( pDimName, '', ParentName, 'n' );
DimensionElementInsert ( pDimName, '', ChildName, 'n' );
DimensionElementInsert ( pDimName, ParentName, ChildName, Weight );

That's a very basic example - hopefully gives you a pointer.

[jviegas@bi4all.pt]

Hi rmackenzie,

My problem is that by defining the odbc by code I don't have access to the metadata and data tab.

Picking from your example

Prolog

Code: Select all

DatasourceUsername=CellGetS(user in admin cube);
DatasourcePassword=CellGetS(password in admin cube);
ODBCOpen('ODBC Source',DatasourceUsername,DatasourcePassword);
DatasourceQuery = Expand (' sql using pDimName as parameter
SELECT DimName, ParentName, ChildName, Weight FROM SOURCE_TABLE
' ); 
ODBCOutput('ODBC Source',DatasourceQuery );

Then where and how should I use

Code: Select all

DimensionElementInsert ( pDimName, '', ParentName, 'n' );
DimensionElementInsert ( pDimName, '', ChildName, 'n' );
DimensionElementInsert ( pDimName, ParentName, ChildName, Weight );

Metadata and Data is disable because I don't have the datasource with any (it was coded in prolog) and I only have epilog available.

I honestly never had to do this like that but in this case I have too.

Regards,

Jorge

[rmackenzie]

Just define one TI and set the username, password and query to something that gives you 4 variables but using parameters e.g.:

Code: Select all

SELECT DimName, ParentName, ChildName, Weight 
FROM '?SOURCE_TABLE?'
WHERE DimName = '?pDimName?'

At runtime, you can update the user/ password variables and the parameters:

Code: Select all

DatasourceUsername=CellGetS(user in admin cube);
DatasourcePassword=CellGetS(password in admin cube);
# parameters
SOURCE_TABLE = 'YOUR_SOURCE_TABLE';
pDimName = 'YOUR_DIMENSION_TABLE';

Because you already set-up this TI with an ODBC data source, the Metadata and Data tabs are available for you to put code into.

[jviegas@bi4all.pt]

Eheheh wouldn't imagine that to be the way. Going to try it and give a feedback (hopefully positive ).

Thanks for the explanation and time

[rmackenzie]

Eheheh wouldn't imagine that to be the way. Going to try it and give a feedback (hopefully positive ).

Thanks for the explanation and time

No problem - good luck. The trick is to keep the SQL queries in a standard format for the way you retrieve the columns.

[jviegas@bi4all.pt]

rmackenzie,

Worked perfectly.

Because I have the sql statement with the columns and I'm needing this because I'm building an admin cube with the user and password for each environment (dev, qa, prod) I don't have the issues with the columns.

So I've tested in a ti that is working and changed the prolog with :

Code: Select all

DatasourceC=CellGetS('ADM_ODBC', 'Connection','PROD');
DatasourceUsername=CellGetS('ADM_ODBC', 'UsrLogon','PROD');
DatasourcePassword=CellGetS('ADM_ODBC', 'UsrPassword','PROD');
ODBCOpen(DatasourceC,DatasourceUsername,DatasourcePassword);
DatasourceQuery = Expand ('SELECT a,b,c from table;'); 
ODBCOutput(DatasourceC,DatasourceQuery );

(ADM_ODBC is the cube with the connections, user and passwords).

Run the TI process and everything worked with the variables created and metadata and data that were already build.

Thanks, as I said I wouldn't think this to be done like this

[declanr]

rmackenzie,

Worked perfectly.

Because I have the sql statement with the columns and I'm needing this because I'm building an admin cube with the user and password for each environment (dev, qa, prod) I don't have the issues with the columns.

So I've tested in a ti that is working and changed the prolog with :

Code: Select all

DatasourceC=CellGetS('ADM_ODBC', 'Connection','PROD');
DatasourceUsername=CellGetS('ADM_ODBC', 'UsrLogon','PROD');
DatasourcePassword=CellGetS('ADM_ODBC', 'UsrPassword','PROD');
ODBCOpen(DatasourceC,DatasourceUsername,DatasourcePassword);
DatasourceQuery = Expand ('SELECT a,b,c from table;'); 
ODBCOutput(DatasourceC,DatasourceQuery );

(ADM_ODBC is the cube with the connections, user and passwords).

Run the TI process and everything worked with the variables created and metadata and data that were already build.

Thanks, as I said I wouldn't think this to be done like this

Why are you doing an ODBCOpen/Output/Close if all you are trying to do is set the datasource query? You have already done that by specifying the DataSourceQuery, I would also consider adding DataSourceNameForServer just for completeness sake.

[jviegas@bi4all.pt]

declanr,

What I need is to have a datasource defined (datasource, user, password, query) to support a TI process.
At this moment I did it normally using the wizard but in the future I will need to do it by code in prolog since I will need to get the user and pass form another cube.

[declanr]

declanr,

What I need is to have a datasource defined (datasource, user, password, query) to support a TI process.
At this moment I did it normally using the wizard but in the future I will need to do it by code in prolog since I will need to get the user and pass form another cube.

Yes, which is why I asked.

The following as the name suggests are to do with setting a datasource:
DataSourceType
DataSourceNameForServer
DataSourceUserName
DataSourcePassword
DataSourceQuery


The following are not:
ODBCOpen
ODBCOutput
ODBCClose

You do not need them.

[jviegas@bi4all.pt]

Uhmmm

Can I use "ops"

Thanks for the extra learning minutes... As I said before I never had to do it like this since I always got a service account to do this.
New challenges more learning

Again thanks for checking this and for the time. Going to change this.

Kind Regards,

Jorge