Basar wrote:
My question is, can anyone point me to a guide or tutorial that explains how to upload data into a cube with a process sheet? I have looked online (on google of course) for 2 days but couldn't find anything useful. I also checked TM1 Help through the installation files and I found out a little bit about TurboIntegrator but I don't think it's good for uploading data from an Excel file, no? So at the end, I am looking for a tutorial or guide that will explain, even briefly, how to upload data to a previously created cube.
Sooo... is the data source "an excel file", or "a previously created cube"?
In neither case will a process sheet help you, at least not directly.
A process sheet is an excel worksheet which has one or more DBS or DBSW formulas on it. (See the Help system for a description of that function; essentially it will send a specified value to a cube. You specify which cell the value is located in, the name of the cube that you're sending to, and one N level element from each of the target cube's dimensions, in the order of those dimensions.) You can use any other Excel or TM1 formulas on the sheet as well if you need to; eg, VLookups if you need to look up an element name that corresponds to one of the values in the data source.
You place such formulas in row 2 or lower. Row 1 is reserved for the data.
The data source for a process worksheet is a comma delimited file. When you run the process, it reads each row of the .cma file into row 1 of the worksheet. It then recalculates the worksheet, and the DBS formula will send the value up to the cube.
Creating a process sheet is often faster than creating a TurboIntegrator process (for new users, at least, older hands can pound out TI code at prodigious rates), HOWEVER:
- A process sheet will be much, much, MUCH slower than a TI process and the later your version, the slower it will be.
- Iboglix regards process sheets as "depreciated" functionality, which is why they can't be bothered fixing the slow speed in newer versions.
Note that TurboIntegrator can use a comma delimited file as a data source as well. If the source file has all of the element names in separate columns and you don't need to manipulate them in any way, you can simply use a CellPutN function (which is equivalent to an Excel DBS formula) in the data tab of the TI process. I do recommend that you do it that way.
If the data source is an Excel file, then no, you can't use TI directly. (If possible, you may be able to export the Excel data into a .cma file and use that, but T.I. can't read or write to Excel files directly.)
You can, however, enter DBS or DBSW functions into the Excel file, and recalculate it which will send the data up to the cube.
If the data source is a previously created cube, AND it is loaded on the same server, then create a view of the data. Often you'll want to do this via the Export function (right click on the cube, you'll find it there) rather than in the Cube Viewer since you won't want title dimensions and row dimensions. You then use a TI process with a data source of Cube View; you specify the name of the view that you've created, and the data will be read. Again you can use a CellPutN on the data tab to write to the new cube.
Also, if the data source is a previously cube and you still want to use a process sheet, you'd need to export the data from the cube into a comma delimited file first.
If you have any specific follow-up questions, please feel fee to ask.
