TI Code vs SQL Syntax

[jimicron]

Hi all,

I am so used to pinging a .csv using a Microsoft Text Driver and using SQL to filter out things I do not want. However, as you are aware, Microsoft doesn't have a 64-bit "Microsoft Text Driver." In another post of mine, we went off topic about this very issue but I wanted to create a new topic specifically for this.

We had some IBM gurus out to our place last week and they were telling me to put this sort of code on the "Data" subtab of the "Advanced" tab:

If(vMPN@= 'CT%');
ItemSkip;
EndIf;

What I am doing is creating a dimension (MPN), and then also populating various attributes from a .csv. An example of what I am pulling from is below in "Diagram A"

vMPN = Dimension
vProductFamily = Attribute of vMPN
vDensity = Attribute of vMPN
vSegment = Attribute of vMPN

I ONLY want to pull in the "vMPN" that have a "vProductFamily" of "MCP"

If I were writing SQL against it, I would do this:

SELECT
vMPN ,
vProductFamily ,
vDensity ,
vSegment
FROM
datasource
WHERE
vProductFamily = 'MCP'

But, I am struggling with how to do this on the 'Advanced' tab I have tried many things and I keep on getting ALL the vMPN's listed and not JUST the ones that have "MCP" as an attribute (vProductFamily).

This is such a core way of doing thing (SQL against .csv) that this is new to do it this way in TM1 (I am very fluent with Cognos Analyst, not TM1 - yet)

Thanks a lot (as always!!)

[David Usherwood]

Not that tough I believe:

Code: Select all

# before generated code
if(vproductfamily@<>'MCP);
itemskip;
endif;

[lotsaram]

However, as you are aware, Microsoft doesn't have a 64-bit "Microsoft Text Driver." In another post of mine, we went off topic about this very issue but I wanted to create a new topic specifically for this.

Well actually Microsoft do have a 64bit text driver. It is included with 64bit Access and also available "stand alone" as part of the 64bit Access DB Engine. The problem however is that Microsoft doesn't support side by side instal of both 32 and 64 bit drivers. If there are any 32 bit Office components installed on the machine at all then the 64 bit drivers will not install. This rules this out for any TM1 server with Excel installed, which is probably the majority of installs.

[Wim Gielis]

In David's suggestion, please add the ' after MCP
but other than that typo, it's the way to go here.

[jimicron]

Thanks guys

Yup - I thought it should be THAT easy as well... but

And, that is the code that I used ABOVE the autogenerated on the Data subtab of the Advanced tab:

Code: Select all

If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

However, what it's doing is still brining in ALL MPN's but then only populating the attributes of the MPN's that have "MCP" as vProductFamily. Whereas, in a SQL statement, it wouldn't include the MPN's that don't have "MCP" as Product Family. The hope is to see the same behavior where I would only get the MPN's that have "MCP" as vProductFamily and not all the rest.

Thanks again for your help!

[jimicron]

However, as you are aware, Microsoft doesn't have a 64-bit "Microsoft Text Driver." In another post of mine, we went off topic about this very issue but I wanted to create a new topic specifically for this.

Well actually Microsoft do have a 64bit text driver. It is included with 64bit Access and also available "stand alone" as part of the 64bit Access DB Engine. The problem however is that Microsoft doesn't support side by side instal of both 32 and 64 bit drivers. If there are any 32 bit Office components installed on the machine at all then the 64 bit drivers will not install. This rules this out for any TM1 server with Excel installed, which is probably the majority of installs.

Hi lotsaram,

Yeah, we had a ticket open with Microsoft as well and there were two things that make it unsupported. One, is that you can't have both 32-bit and 64-bit installed at the same time and two, it's the way TM1 calls or uses the DNS. It uses the DNS on the server, and not the one on your desktop. As such, multiple people can hit that DNS on the server and that's another reason he said it wasn't supported. WIth Cognos Planning Analyst, if you use ODBC's to connect to .csv files (for example), you add the DNS (using "Microsoft Text Driver") on your desktop and that is what ANalyst uses. However, Tm1 uses the DNS added on the server.

We were having intermittent crashes with not only the "Microsoft Access Text Driver" (64bit) on the Tm1 server, but it also did something to cause intermittent crashes of the server when also pinging our Oracle server. We've had extensive talks with both IBM Techs, IBM developers, IBM team support, and Microsoft Developers and team leads.

The intermittent instability is unfortunate because we are now writing .csv's to a SQL server and then pinging the SQL server. It's adding an extra layer of complexity though and also we now have to have our IS dept involved so it's not ideal, although it's getting us what we need. I have actually gone into other processes and used the same sort of syntax as I am trying to figure out here on the Data tab so we are filtering in the TM1 process, but this one is giving me fits and not doing what I want it to do.

Anyway, just wanted to comment on that - don't mean to get this thread off topic I should ask you in PM sometime... 'lotsaram' .... if I know you... haha.

[David Usherwood]

However, what it's doing is still brining in ALL MPN's but then only populating the attributes of the MPN's that have "MCP" as vProductFamily. Whereas, in a SQL statement, it wouldn't include the MPN's that don't have "MCP" as Product Family. The hope is to see the same behavior where I would only get the MPN's that have "MCP" as vProductFamily and not all the rest.

Could you elucidate why that isn't right? TI @<> SQL (insider TM1 joke here, apologies) so you're just reading and throwing away the other records. TI reads flat files blazingly fast so I doubt it is unworkable.

[jimicron]

Hi David

SO are you saying that this isn't possible in a TI Process? That really stinks.

I am not sure I am following your comments though. Yes, in a SQL statement, when you limit what you pull in from a .csv, sql database, or oracle database by using a WHERE clause, it only pulls in what you ask. For example, there may be a table with 100,000 records on it and only 100 of those have "MCP" as a "Product Family" so when you say
SELECT such and such FROM such and such WHERE "Product Family" = 'MCP' it will only pull in the 100 records where "Product Family" is "MCP" not pull in the remaining 99,900 records like TM1 is doing. Sigh.

Right now, with that statement above, it's pulling in all records and then just populating the ones where Product Family is "MCP" - I don't want the records if the Product Family isn't MCP.

Make sense? Thanks.

[declanr]

Right now, with that statement above, it's pulling in all records and then just populating the ones where Product Family is "MCP" - I don't want the records if the Product Family isn't MCP.

The code that was provided above:

Code: Select all

If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

If placed at the top of your data and metadata tabs will skip every record where the Product Family isn't MCP, isn't that exactly what you are asking for?

Just to clarify, if used correctly - this code will make it so that no subsequent lines of code are processed against a data record unless the Product family is MCP which is pretty much the equivalent of a SQL where clause.

Of course if you can upload your CSVs into SQL views (which isn't all that hard to do) you could then just use a query with a where clause against that SQL view. That would never be my first choice but if you aren't comfortable working within TI and would prefer the SQL approach it is always an option.

[jimicron]

Declanr said: ...That would never be my first choice... AMEN to that!!

Thanks a lot!! THAT just worked!! I am now only getting the MPN's that have Product Family of MCP. EXCELLENT!!!!

And what I did was put this:

Code: Select all

If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

In BOTH the "Metadata" -and- "Data" tabs under "Advanced" ABOVE the autogenerated code.

I was curious though and so I kept it on the "Metadata" tab but got rid of it on the "Data" tab and it appears it did the same thing. I looked at the attributes and they were populated. OR, am I missing something where I am maybe getting lucky? It's not hard to put it in both tabs... and it's doing exactly what I want.. but was just curious.

Thanks a lot! Makes my day!

[declanr]

Thanks a lot! Makes my day!

I would like credit but all I did was copy and paste the first response you got from David Usherwood above.

Was the issue simply that you weren't putting it BEFORE the other code previously?

I was curious though and so I kept it on the "Metadata" tab but got rid of it on the "Data" tab and it appears it did the same thing. I looked at the attributes and they were populated. OR, am I missing something where I am maybe getting lucky? It's not hard to put it in both tabs... and it's doing exactly what I want.. but was just curious.

Got a bit lost in there, the basics are:

• TIs process the whole data source for the data tab and metadata tab separately, so an Item Skipped (by the ItemSkip function) on one tab will still appear on the other (unless skipped on that tab also.)
• The ItemSkip function needs to occur BEFORE any other functions, it essentially hits the ItemSkip and interprets it as the end of the tab for that data record.
• If an ItemSkip is encountered at the top of a tab, the data record is transacted at an incredibly quick pace - almost as if it was never there.

[jimicron]

No, we have been putting it BEFORE the autogenerated all along. The issue was that I only had it on the "Data" tab and it was NOT on the "Metadata" tab. But, by putting it in both places, it's working just as expected.

Maybe part of David's inside joke was the whole @<> thing... which is just a different thought process than my humble brain... so, out of curiosity again, I tried this on both "Data" and "Metadata" tabs:

Code: Select all

If(vProductFamily@='MCP');
EndIf;

But, that didn't work. I was thinking... okay... let's call an ace and ace... so in my statement... just say what I want and that is when "Product Family" = 'MCP' ... but it's like it needs something else... something to replace the "itemskip" like "iteminclude" (or something like that). SOmething that says, hey, if it = this... then give it to me.

At least I am out of my bind. I am just trying to learn further and understand b/c it will help me so much more in building these models and teaching others. Appreciate the help as always!

[declanr]

No, we have been putting it BEFORE the autogenerated all along. The issue was that I only had it on the "Data" tab and it was NOT on the "Metadata" tab. But, by putting it in both places, it's working just as expected.

Maybe part of David's inside joke was the whole @<> thing... which is just a different thought process than my humble brain... so, out of curiosity again, I tried this on both "Data" and "Metadata" tabs:

Code: Select all

If(vProductFamily@='MCP');
EndIf;

But, that didn't work. I was thinking... okay... let's call an ace and ace... so in my statement... just say what I want and that is when "Product Family" = 'MCP' ... but it's like it needs something else... something to replace the "itemskip" like "iteminclude" (or something like that). SOmething that says, hey, if it = this... then give it to me.

At least I am out of my bind. I am just trying to learn further and understand b/c it will help me so much more in building these models and teaching others. Appreciate the help as always!

Of course putting the rest of your code inside of the:

Code: Select all

If(vProductFamily@='MCP');

Rest of Code


EndIf;

Would have exactly the same effect as ItemSkipping all those things where Product Family isn't MCP.

[jimicron]

So, you are saying do this?

Code: Select all

If(vProductFamily@='MCP');
If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

Seems easier to just say this:

Code: Select all

If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

I am not seeing anything that is the opposite of "ItemSkip' ... b/c my thought was to do something like this:

Code: Select all

If(vProductFamily@='MCP');
ItemInclude;
EndIf;

I know that "ItemInclude" doesn't exist.. but that is what I was hoping... or wondering... if there was something equivalent to it. Not a biggie if not. Again, I am over my hurdle, I was just trying to learn more and understand Thanks again!!

[declanr]

So, you are saying do this?

Code: Select all

If(vProductFamily@='MCP');
If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

Seems easier to just say this:

Code: Select all

If(vProductFamily@<>'MCP');
ItemSkip;
EndIf;

I am not seeing anything that is the opposite of "ItemSkip' ... b/c my thought was to do something like this:

Code: Select all

If(vProductFamily@='MCP');
ItemInclude;
EndIf;

I know that "ItemInclude" doesn't exist.. but that is what I was hoping... or wondering... if there was something equivalent to it. Not a biggie if not. Again, I am over my hurdle, I was just trying to learn more and understand Thanks again!!

No, I mean include the Code that you had before this thread was even started inside the If statement (for example CellPutN functions - if you had them.)

Code: Select all

If ( vVariable @<> 'Turnip' );
      ItemSkip;
EndIf;

CellPutN ( Value, sCub, v1, v2, v3, 'Measure');

Will have the same outcome as:

Code: Select all

If ( vVariable @= 'Turnip' );
     CellPutN ( Value, sCub, v1, v2, v3, 'Measure');
EndIf;

[jimicron]

Thank you!