Upload Cubeview to MS Access

Post Reply
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Upload Cubeview to MS Access

Post by appleglaze28 »

I need some assistance on how to upload a cubeview to MS Access. I'm not familiar with databases.

PROLOG
odbcopen('TM1_Test', 'Admin', 'password');

DATA
odbcoutput('TM1_Test', EXPAND('INSERT INTO TM1_Test ([Version], [Scenario], [Currency], [Month], [Year], [Rate_Assumption_m], [Data[) Values ('Version','Scenario','
Currency','vMonth','vYear','Rate_Assumption_m',Value)'));

EPILOG
odbcclose ('TM1_Test');


I read in some post that with the bracket is applicable for MS Access...with other databases you can just remove the bracket and it will work? Since I plan to test this on SQL Server
Attachments
Error on Saving TI
Error on Saving TI
TI Error.JPG (10.98 KiB) Viewed 8455 times
Screenshot of Variables of Cubeview
Screenshot of Variables of Cubeview
TM1-TI Process_ODBCOutput.JPG (62.21 KiB) Viewed 8456 times
MS Access Table
MS Access Table
TM1-AccessODBC.JPG (61.71 KiB) Viewed 8455 times
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Upload Cubeview to MS Access

Post by Alan Kirk »

appleglaze28 wrote:I need some assistance on how to upload a cubeview to MS Access. I'm not familiar with databases.
I'm not going to redo this in line with your latest variation in this post, but with regard to the example in http://forums.olapforums.com/viewtopic.php?f=3&t=932...

Assume that you have a cube which has the dimensions that you specified in that thread; Version, vMonth, vYear, Measure and Revenue_Center. You create a view from that cube. Your variables are therefore those dimension names, plus one variable called Value.

All of the dimensions are set to a Variable Type of String. "Value" is set to Numeric. All variables have their Contents set to "Other".

You have an Access database which has a table called RC, which has fields with the same name as your variables. All of the dimension name fields are Text fields, and the Value field is a Numeric (Double) field. This database has an ODBC data source name of "Test".

You do NOT have the database open. The database is NOT secured by a password or user level security.

Your Prolog is:

Code: Select all

# Make sure that you don't have the database open or there may be a lock
# on the table.

OdbcOpen('Test', 'Admin', '');

# This is the single quote character. By making it a variable
# (constant) it's easier to see where it's being used
# as part of the SQL statement as opposed to normal
# TI syntax.
SC_SINGLEQUOTE = Char( 39);
Your Data tab is:

Code: Select all

# The Value field name is surrounded in square brackets to ensure that Access knows that
# it's a field name. The other field names don't need it, but Value does because there's a 
# Value function that you can use to define fields in Access queries. The square brackets
# remove that ambiguity.

# The string values (element names) that need to be fed into the text fields need to be surrounded by
# single quotes, which I've defined as a variable (constant) to improve visibility. 
# The Value variable doesn't need single quotes, but it DOES need to be converted
# to a string so that it can be added to the s_SQL string.

# Both the list of field names, and the values that are going to be fed into them
# need to be surrounded by brackets (parentheses).

s_SQL = 'INSERT INTO RC ( Version, vMonth, vYear, Measure, Revenue_Center, [Value] ) ' 
 | 'Values ('
 | SC_SINGLEQUOTE | Version  | SC_SINGLEQUOTE | ', '
 | SC_SINGLEQUOTE | vMonth  | SC_SINGLEQUOTE | ', ' 
 | SC_SINGLEQUOTE |  vYear  | SC_SINGLEQUOTE | ', ' 
 | SC_SINGLEQUOTE | Measure  | SC_SINGLEQUOTE | ', ' 
 | SC_SINGLEQUOTE | Revenue_Center  | SC_SINGLEQUOTE | ', ' 
 | Trim ( Str( Value, 12, 2 ) ) | ')';

# THIS is what David was referring to.
AsciiOutput ('D:\TM1\Temp\SQLStuff.txt', s_SQL );

ODBCOutput('Test', s_SQL);
Edit: Just for clarity, David was suggesting doing the ASCIIOutput INSTEAD OF the ODBCOutput while you're developing it. The intention is just to make sure that the syntax looks right. Once you're certain that it is, you no longer need it.

------------------------------
Further Edit Just to make it clearer what the Insert Into statement is doing (though this is more of an SQL thing than a TI thing)...

Insert Into has a couple of different syntaxes. You may need to check the documentation of your SQL database (be it Access, SQL Server, Oracle, etc) to confirm any issues that may arise. However the one we're using here is a pretty "vanilla" syntax. It's essentially:
Insert Into TableName (FieldName1, FieldName2, FieldName3, etc) Values (Value1, Value2, Value3, etc)

That is, you have a pair of groups inside the brackets. The first one states which fields of the Access table you want to add values to, the second, what the values that you want to add are. Each value in the second group corresponds to a field name in the first group.

The names in the first group don't need to have quote marks around them. If the name is potentially ambiguous (in Access at least) you should surround it with square brackets as we've done here with the Value field.

The values in the second group should be surrounded by single quotes if they're going to a text field. For example, 'Jan', '2009' and so on.

If they're going to a numeric field, don't put single quotes around them.
------------------------------

Your Epilog is:

Code: Select all

OdbcClose('Test');
This has been tested and I've confirmed that it works. Just modify it as you need to.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: Upload Cubeview to MS Access

Post by appleglaze28 »

I'd like to clarify if there is a specific order of the field or variable name when writing the TI as the cube or the table? Or as long as both field & variable on the same order.

I was successful loading the data from 1 cube to another however, when I try to load more dimension elements for example 8 dimension, I notice that the data coming from the cube. I'm not sure what was different with this scenario since i just added the additional dimension names in the TI to fit the cubeview and the datasource I'll load the data in.

The statement below is the output of the ASCIIOutput
"INSERT INTO Month_Prod (Version, Year, Store, Currency, Scenario, Sales_m, Product, Month, Data)Values (2Version 12,220042,2Dandy Delights2,2Local Currency2,2Budget2,2Purchases, Units2,2Portsmouth Imported Beer2,2January2,500.00)"

I'm wondering why there are "2" either infront or after the element.
Attachments
TI Process.JPG
TI Process.JPG (81.44 KiB) Viewed 8400 times
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Upload Cubeview to MS Access

Post by Alan Kirk »

appleglaze28 wrote:I'd like to clarify if there is a specific order of the field or variable name when writing the TI as the cube or the table? Or as long as both field & variable on the same order.
Makes no difference to either Access or TI. (Edit: That is, it makes no difference what order you have the fields in in the list. You DO obviously have to have the first value in the values list relating to the first field in the fields list, the second value relating to the second field and so on.)
appleglaze28 wrote:I was successful loading the data from 1 cube to another however, when I try to load more dimension elements for example 8 dimension, I notice that the data coming from the cube.
Buh???
appleglaze28 wrote: I'm not sure what was different with this scenario since i just added the additional dimension names in the TI to fit the cubeview and the datasource I'll load the data in.
Check your syntax. Usually when you run into problems, then as David indicated in the earlier post it's because you're missing quotes or a bracket or some such thing. This is where the ASCIIOutput that he suggested comes in handy. The second most common cause is if you're trying to write the wrong type of data to the wrong type of field. (Eg text to a numeric field.) If you still can't figure it out, best to post another screenshot of your TI, the code that you're using and the error that you're getting.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: Upload Cubeview to MS Access

Post by appleglaze28 »

Thanks for you help Alan, greatly appreciated.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Upload Cubeview to MS Access

Post by Alan Kirk »

appleglaze28 wrote:I'd like to clarify if there is a specific order of the field or variable name when writing the TI as the cube or the table? Or as long as both field & variable on the same order.

I was successful loading the data from 1 cube to another however, when I try to load more dimension elements for example 8 dimension, I notice that the data coming from the cube. I'm not sure what was different with this scenario since i just added the additional dimension names in the TI to fit the cubeview and the datasource I'll load the data in.

The statement below is the output of the ASCIIOutput
"INSERT INTO Month_Prod (Version, Year, Store, Currency, Scenario, Sales_m, Product, Month, Data)Values (2Version 12,220042,2Dandy Delights2,2Local Currency2,2Budget2,2Purchases, Units2,2Portsmouth Imported Beer2,2January2,500.00)"

I'm wondering why there are "2" either infront or after the element.
I have a feeling that you've mis-defined the SC_SINGLEQUOTE constant in the prolog tab. It looks like you have it defined as "2", possibly as Char(50) instead of Char(39).

Be careful of Month and Year too; like Value, those can be used as functions in Excel to define a field in a query. You may need to put square brackets around them too. (In your original example it was vMonth and vYear, so that didn't matter.)

Easiest way to check the syntax is to copy one line from your ASCIIOutput, go to Access, create a new query, go into SQL view, paste the line in, and see whether you can save it as a query. If you can't, Access will give you an error message.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: Upload Cubeview to MS Access

Post by appleglaze28 »

I'd like to ask what would be the best way to know why some of the data in the cube view aren't goin in the database and I get an TI abort message? I tried changing my cube by loading just 1 specific element of a dimension, some TI run perfectly while others don't.

I'm testing the load TM1 data to a database to work on using the data for Cognos BI, but with some data not coming in its hard to test if all data are coming in correctly. I'm using the MS SQL sample database Northwind.

Since I'm not familiar with databases, when you run TI...does it override all the content of that table or does it add more rows? Since everytime I run TI it seems like the table adds more rows.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Upload Cubeview to MS Access

Post by Alan Kirk »

appleglaze28 wrote:I'd like to ask what would be the best way to know why some of the data in the cube view aren't goin in the database and I get an TI abort message? I tried changing my cube by loading just 1 specific element of a dimension, some TI run perfectly while others don't.

I'm testing the load TM1 data to a database to work on using the data for Cognos BI, but with some data not coming in its hard to test if all data are coming in correctly. I'm using the MS SQL sample database Northwind.

Since I'm not familiar with databases, when you run TI...does it override all the content of that table or does it add more rows? Since everytime I run TI it seems like the table adds more rows.
Someone who's more familiar with the SQL version of Northwind (and SQL Server itself) may be better equipped to answer that, but I can give you some general advice. (I'm familiar with Northwind up to Access 2003, but I can't even get the Access 2007 version to work under Vista. Not that it matters considering how MS has cactused every database that uses user level security unless you want to stay with the old format database... in which case you may as well be running Access 2003 instead. Which, indeed, I do. But I digress.)

In general terms:
- If the TI is aborting because it can't execute the SQL statement, the reasons are usually one of these three:
(a) You don't have the necessary permissions to the table (which is unlikely with your Access example, but possible with your SQL Server example though it would seem to be unlikely that you could write ANY data in such a case); or
(b) You're attempting to write data which violates a data integrity rule. (For example, the table may have a key which requires that a particular field be unique. An example might be an invoice number; you can have an invoice number on only one row in the table. If you try to write another row with the same invoice number, the attempt will fail. This is only one example.)
(c) You're trying to write data to a field which has the wrong data type; for example, writing text to a numeric field.

The SQL statement that you were using (Insert Into) does indeed add new rows every time it runs. That's the intention of the syntax. If you wanted to modify records which already exist you'd need to use an update query.

If you're really unfamiliar with SQL syntax, it may be worth seeing whether you could get a contractor or consultant who's familiar with TM1 and Cognos BI in for even a day or so to get you on the right track; the cost may end up being less than the time that you'll burn up in getting up to speed on this on your own.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
appleglaze28
Regular Participant
Posts: 269
Joined: Tue Apr 21, 2009 3:43 am
OLAP Product: Cognos TM1, Planning
Version: 9.1 SP3 9.4 MR1 FP1 9.5
Excel Version: 2003

Re: Upload Cubeview to MS Access

Post by appleglaze28 »

I just find it odd since the database is locally install in my pc and I should have full right to the database and its tables. The table I created is a general table with all fields as "varchar" datatype except for the value having it as numeric. So I think its weird that the SQL query is wrong for some data but to some the data loads on the table.

I was thinking it could probably be some unique character not common to the English language I realize that special character shouldn't stop the data from being loaded since the data itself was from MS SQL.
Post Reply