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);
: 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:
This has been tested and I've confirmed that it works. Just modify it as you need to.