Cube ImportCells

Writes multiple OLAP cell values/notes.

You can aggregate data using the year to date functionality. See the "Writing YTD data" topic.

Request

In this request, replace \t with an actual horizontal tabulator.

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request RequestID="001" Class="Cube" Method="ImportCells">
    <Alea:CellBatch Cube="Sales" BreakOnError="false" Server="SAMPLES">01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS700/245\tSales Volume\t981
01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS600/175\tSales Volume\t3164
01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS600/185\tSales Volume\t\t"cell note ^ttest1^^^r^ntest2" 
-1\tActual\tEUR\tIFRS\tC1101\tC1201\tAS600/205\tSales Volume\t3164
01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS700/205\tSales Volume\t[+]3164
01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS700/225\tSales Volume\t%#DELETED#%\t%#DELETED#%</Alea:CellBatch>
  </Alea:Request>
</Alea:Document>

The Server attribute is used in the header line of the error return alongside the cube name. It can be used to identify the source without direct relation to the actual request. This attribute can be useful for identifying import issues. This attribute is optional.

If there are multiple <Alea:CellBatch> tags, only the first one is processed. Consecutive tags are ignored, and no error message is returned.

The Format attribute can be used to specify a second format to send the element names. If Format=Incremental, a missing string is replaced with the string from the line before. This makes the request smaller and reduces the time needed to process the request. This attribute is optional.

If the BreakOnError attribute is set to false, any invalid lines are skipped, and the import continues. The invalid lines are logged. If BreakOnError is set to true, the request stops at the first line and nothing is applied.

The WithHierarchies attribute defines that for each dimension two columns are expected inside the CellBatch tag. First a column with the hierarchy name followed by a column with the element name. For example: 

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request Class="Cube" Method="ImportCells">
    <Alea:CellBatch BreakOnError="true" Cube="Name" WithHierarchies="true"> Dim1Hier Dim1Elem Dim2Hier Dim2Elem value </Alea:CellBatch>
  </Alea:Request>
</Alea:Document>

Element names, hierarchy names, the value, and cell notes are tab delimited ('\t' : 9), each line ends with CRLF:"\r\n" or by LF:"\n".

String values and cell notes can be enclosed in quotation marks. If you want quotation marks at the beginning or at the end of the resulting string value or cell note, there must be two quotation marks. If there are escaped characters in the string value or cell note and they are supposed to be converted, the string value or cell note must be enclosed in quotation marks.

Empty values and notes are ignored.

(*) escape sequences to encode text elements into structured format

^^ : '^' : 94

^t : '\t' : 9

^r : '\r' : 13

^n : '\n' : 10

The sample is converted as: 
cell note test1^
test2

The syntax [+]<value> requests the value to be added to the current cell instead of overwriting its value. In this request, replace \t with an actual horizontal tabulator.

01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS700/205\tSales Volume\t[+]3164

2003 Actual USA Ultranote 386/25sx BW January Sales [+]3164

The token %#DELETED#% is used to delete the value and/or note of a cell during the import. In this request, replace \t with an actual horizontal tabulator.

01_2014\tActual\tEUR\tIFRS\tC1101\tC1201\tAS700/225\tSales Volume\t%#DELETED#%\t%#DELETED#%

Both value and comment can be specified delimited by a tab.

Answer

In this answer, actual horizontal tabulators are returned instead of \t.

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request RequestID="001">
    <Alea:Return>SAMPLES\tSales\t-1\tActual\tEUR\tIFRS\tC1101\tC1201\tAS600/205\tSales Volume\t3164\t65\t0
</Alea:Return>
  </Alea:Request>
</Alea:Document>

The Return element contains the lines in which the errors occurred. If no error occurred, the Return element is empty. A line is tab delimited and contains this information:

  • The server indication from the request.
  • The name of the cube.
  • The line from the ImportCells request.
  • The error code for writing the cell value.
  • The error code for writing the cell note.

These are the error codes:

  • 0: No error.
  • 65: The dimension element could not be found.

Error

This error is returned if, for example, the cube does not exist, no rights to write or synchronization problems. 

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request RequestID="001">
    <Alea:Error ErrorID="error_code"/>
  </Alea:Request>
</Alea:Document>

Using custom delimiters

The Delimiter attribute is used to specify a custom delimiter instead of the default horizontal tabulator (ASCII 9). The custom delimiter must be just one character long. In the UTF-8 representation, it must not be encoded in more than 1 byte. The null character (ASCII 0), new line character (ASCII 10), and carriage return (ASCII 13) are not allowed. You must ensure that the character used as a delimiter does not appear anywhere else on the imported lines, such as in the element/hierarchy names, cell values, cell notes, in the %#DELETED#% tag, and so on. A space (ASCII 32) must be used carefully because a space can appear in the text values. You cannot escape the custom delimiter with a ^.

When importing numbers as cell values, the decimal separator can be either a period (.) or comma (,). Both separators are recognized. For example:

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request RequestID="001" Class="Cube" Method="ImportCells">
    <Alea:CellBatch Cube="Sales" BreakOnError="true" Server="SAMPLES" Delimiter=";">01_2014;Actual;LC;IFRS;C1101;C1201;AS700/225;unassigned;400.111;cell note number one
01_2014;Budget;LC;IFRS;C1101;C1201;AS700/225;unassigned;200,456;cell note number two
01_2014;Forecast;LC;IFRS;C1101;C1201;AS700/225;unassigned;100,789;cell note number three</Alea:CellBatch>
  </Alea:Request>
 </Alea:Document>

Importing cell notes

The OnlyNotesColumn attribute is used to import cell notes. It does not import cell values. The allowed values for the OnlyNotesColumn attribute are true or false. The default value is false, which has no effect. When setting the OnlyNotesColumn attribute to true, OLAP expects the Note column in an imported row but no Value column.

<Alea:Document xmlns:Alea="http://www.misag.com">
  <Alea:Request RequestID="001" Class="Cube" Method="ImportCells">
    <Alea:CellBatch Cube="Sales" BreakOnError="true" Server="SAMPLES" Delimiter=";" OnlyNotesColumn="true">01_2014;Actual;LC;IFRS;C1101;C1201;AS700/225;unassigned;cell note number one
01_2014;Budget;LC;IFRS;C1101;C1201;AS700/225;unassigned;cell note number two
01_2014;Forecast;LC;IFRS;C1101;C1201;AS700/225;unassigned;cell note number three</Alea:CellBatch>
  </Alea:Request>
 </Alea:Document>

Suppressing Writing to AleaLog

For systems where the AleaLog is processed into a relational backend system it is beneficial if the imports executed by this system are not logged again in AleaLog. This is for performance and easier processing.

To facilitate this, the AleaLog attribute on the CellBatch element of the ImportCells function in the cube class can be used:

<Alea:CellBatch AleaLog="false">

This attribute is optional. The default value of this attribute is true. If set to false, the imported values are not logged to AleaLog.

Administrator permissions are required for this attribute. If a request with AleaLog="false" is issued without sufficient permission, this error message is returned, and no data is imported: 

You are not authorized to carry out this operation
Related topics
  • Writing YTD data