MDX Insert Specification

The insert clause is a way you can use MDX to update the Essbase database with new data, by inserting tuples from a source to a target.

MDX Insert is supported for aggregate storage databases and hybrid mode databases.

Syntax

[WITH MEMBER calculated_member_name AS ' <numeric_value_expr> ']
INSERT
         <source_tuple> TO <target_tuple>
         ....
        <source_tuple> TO <target_tuple>
        [<offset> <debitmember> <creditmember>]
        [USING <load_buffer_method>]
INTO
          APP.DB
FROM
 ( 
        <nested_select_statement>
 )
[WHERE [<slicer_specification>]]

Table 4-13 MDX INSERT Clause Elements

Item	Description
source_tuple	A database region from which to retrieve data values. The source tuple can contain dynamic or stored members. It can contain member-based functions, but it cannot contain context-dependent member functions, such as CurrentMember. Examples: "([Scenario].[S1], [Jan])" "([Scenario].[S1])" "([Measures].[Payroll])" Map the source tuple to a target tuple that you will be updating.
target_tuple	The database region to populate with values from the source tuple. The target tuple must consist of only stored members, dynamic calc and store members, or member-based functions. It cannot contain dynamic members. Examples: "([Actual])" "([Actual], [Revised_payroll])" "([Actual], [Year].CurrentMember.PrevMember)"
offset, creditmember, debitmember	Optional parameters for double-entry accounting, applicable only for custom calculations in aggregate storage cubes. For details about these parameters, see Performing Custom Calculations and Allocations on Aggregate Storage Databases
USING load_buffer_method	Optional, and supported only for aggregate storage databases. Specifies the data load buffer method to use when updating the aggregate storage database. Examples: USING Add Values USING Subtract Values If no method is specified, the update replaces values with the contents of the load buffer.
INTO app.db	The cube specification naming the database at which the Insert clause is directed. Must be same as the cube used in the FROM clause of the inner SELECT statement.
FROM nested_select_statement	An inner select statement defining the database region from which the tuples you want to insert should be retrieved.

The WITH section is optional, enabling you to define the area to insert using a calculated member.

The WHERE section is optional, enabling you to define a slicer.

Notes

Do not use attribute dimension members in the source or target tuples.
Do not use context-dependent member functions, such as CurrentMember or PrevMember, in the source tuple.
The source and target tuples should have the same dimensionality. For example, the following source and target tuple have the same dimensionality because the target tuple, [Scenario].[Actual], which is stored, matches the format of the source tuple, [Scenario].[S1], which is a calculated member defined in the WITH section.
```
 “([Scenario].[S1])” TO “([Scenario].[Actual])”
```
#Missing values are not inserted/copied.
Filters assigned to you may limit what regions of data you can insert.
The source cube (app.db) of the INTO clause must be same as the source cube used in the FROM clause of the inner SELECT statement.

Example 4-1 Calculated Member and Nested Select Statement

The following example uses a calculated member, M1, as the source tuple to update a target member, Commission, in Sample Basic.

WITH
           Member [Measures].[M1] as 'Sales * 0.1'
INSERT 
           "([Measures].[M1])" TO "([Measures].[Commission])"
INTO [Sample].[Basic]
FROM ( 
       SELECT 
             {[Measures].[M1]} on columns,
             {(Jan, Actual, [100-10], [New York])} on  ROWS
        FROM  [Sample].[Basic]
);

Example 4-2 Copying Data

The following example uses an inner select statement of crossjoins to copy data from one outline member to another.

INSERT "([Measures].[Payroll])" TO "([Measures].[Revised_Payroll])"
INTO [Test].[Basic]
FROM (
       SELECT 
            {[Measures].[Payroll]} ON COLUMNS,
                {Crossjoin
                  (Crossjoin(Descendants([Year]), 
                   Crossjoin(Descendants([Scenario]),
                  Descendants([Product]))),
                  Descendants([Market]))} ON ROWS
       FROM [Test].[Basic]
);

Example 4-3 Inserting Multiple Tuples

The following example inserts multiple tuples into Test.Basic.

WITH
       Member [Measures].[M2] as 'Sales * 0.5'
INSERT 
       "([Measures].[M2])" 
        TO 
       "([Measures].[Commission])"

       "([East].[New York],[Measures].[Payroll])" 
        TO 
       "([Measures].[Revised_Payroll])"

INTO [Test].[Basic]
FROM (
       SELECT 
            {[Measures].[M2]} ON COLUMNS,
            {Crossjoin(Crossjoin(Descendants([Year]), 
             Crossjoin(Descendants([Scenario]), 
             Descendants([Product]))),  
             Descendants([Market]))} ON ROWS
      FROM [Test].[Basic]
);

Example 4-4 Performing Allocations

The following example uses a calculated member to perform an allocation in the Scenario dimension.

WITH MEMBER 
 [Scenario].[S1] 
AS 
 '([PY Actual], [Total Expenses]) * 
  ([Budget] / ([Total Expenses], [Budget]))'
INSERT 
 "([Scenario].[S1])" 
 TO 
 "([Scenario].[PY Actual])"
INTO 
 [Sample1].[Basic]
FROM 
 (SELECT  
   {[Scenario].[S1]} 
   ON COLUMNS,
    Crossjoin
     (Crossjoin
      ({[Jan]}, 
       Crossjoin([Total Expenses].Children, {[100],[200]})
     ), {[New York]}) 
   ON ROWS
   FROM 
   [Sample1].[Basic]
 );

The above MDX example has similar functionality to a block storage allocation as shown in the following calc script example:

FIX("Total Expenses", {[Jan]}, [[New York]])
"PY Actual" = @ALLOCATE("PY Actual"->"Total Expenses",@CHILDREN("Total Expenses"), "Budget",,share);
ENDFIX

Example 4-5 Inserting Using Member Context

The following example updates the revised payroll based on previous year context.

INSERT 
  "([Measures].[Payroll])" 
TO 
  "([Measures].[Revised_Payroll],[Year].CurrentMember.PrevMember)"

INTO [Test].[Basic]
FROM
 (
  SELECT  
   {[Measures].[Payroll]} 
   ON COLUMNS,
   {Descendants([Year])} 
   ON ROWS
   FROM [Test].[Basic]
   WHERE ([Actual],[100-10],[New York])
 );

Example 4-6 Performing a Custom Calculation

The following example runs a custom calculation on an aggregate storage database.

WITH 

 MEMBER [Amount Type].[AT1] 
 AS 
 'CASE 
   WHEN IS ([Account].CurrentMember, [ACC19802]) 
    THEN ([ACC19802],[CC10000],[ORG63],[Beginning Balance]) 
   WHEN IS([Account].CurrentMember, [ACC19803]) 
    THEN ([ACC19803],[FEB-05/06],[ORG00],[CC20000],[Beginning Balance]) * 2
   WHEN IS([Account].CurrentMember, [ACC19804]) 
    THEN ([ACC19804],[Feb-05/06],[ORG65],[CC19000],[Beginning Balance]) * 
         ([ACC19803],[Feb-08],[ORG63],[CC12000],[Beginning Balance])
   WHEN IS([Account].CurrentMember, [ACC19805]) 
    THEN ([ACC12000],[Beginning Balance]) + ([ACC19802],[Beginning Balance]) + 20
   WHEN IS([Account].CurrentMember, [ACC19806]) 
    THEN ([ACC19805],[Feb-08],[ORG63],[CC12000],[Beginning Balance])- 0.00000020e7
   WHEN IS([Account].CurrentMember, [ACC19807]) 
    THEN 1
   ELSE Missing
  END'

 MEMBER [Amount Type].[AT3] 
 AS 
 'IIF
   ([Amount Type].[AT1] < 0, 
   [Amount Type].[AT1] * -1, Missing)'

 MEMBER [Amount Type].[AT4] 
 AS 
 'IIF
  ([Amount Type].[AT1] >= 0, 
   [Amount Type].[AT1], Missing)'	
							 
 MEMBER [Amount Type].[AT5] 
 AS 
 'IIF(IS([Organisation].CurrentMember, [ORG00]) 
  AND IS([Account].CurrentMember, [ACC19807]),
   SUM(Crossjoin(
    [ACC19801].Children, 
    {[ORGT].Children}),
   [Amount Type].[AT1]), Missing)'
            
 MEMBER [Amount Type].[AT6] 
 AS 
 'IIF
  ([Amount Type].[AT5] < 0, 
   [Amount Type].[AT5] * -1, Missing)'

 MEMBER [Amount Type].[AT7] 
 AS 
 'IIF
  ([Amount Type].[AT5] >= 0,
   [Amount Type].[AT5], Missing)'

INSERT

 "([Amount Type].[AT3])" 
 TO 
 "([Allocations],[Beginning Balance Credit])"

 "([Amount Type].[AT4])" 
 TO 
 "([Allocations],[Beginning Balance Debit])"

 "([Amount Type].[AT6])" 
 TO 
 "([Allocations],[Beginning Balance Debit],[ORG66])"

 "([Amount Type].[AT7])" 
 TO 
 "([Allocations],[Beginning Balance Credit],[ORG66])"

INTO 
   [Gl].[Basic]

FROM 
(
   SELECT 
      {[ACC19801].Children} 
   ON COLUMNS,
      {Crossjoin(Crossjoin([ORGT].Children,[CCT].Children),
      {[Amount Type].[AT1],
       [Amount Type].[AT3],
       [Amount Type].[AT4],
       [Amount Type].[AT5],
       [Amount Type].[AT6],
       [Amount Type].[AT7]})} 
   ON ROWS
   FROM 
    [Gl].[Basic]
   WHERE 
     ([Actual],[PUBT],[OUTT], [Feb-08],[FRED],[ANLT])
);

Example 4-7 Performing a Custom Allocation

The following example runs a custom allocation on an aggregate storage database.

WITH 
 MEMBER [Amount Type].[AT1] 
 AS 
  '([Beginning Balance],[ORG63],[CC10000])'

 MEMBER [Amount Type].[AT2] 
 AS 
 '[Amount Type].[AT1]/
   Count(
    Crossjoin(
     {[Beginning Balance Credit]},
      CrossJoin(
       Descendants(
        [ORGT],
        [Organisation].Levels(0)
       ),
       Descendants([CCT],[Cost Centre].Levels(0))
       )
      )
     )'

 MEMBER [Amount Type].[AT3] 
 AS 
 'IIF([Amount Type].[AT2] < 0, [Amount Type].[AT2] * -1, Missing)'

 MEMBER [Amount Type].[AT4] 
 AS 
 'IIF([Amount Type].[AT2] >= 0, [Amount Type].[AT2], Missing)'

 MEMBER [Amount Type].[AT5] 
 AS 
 'IIF(IS([Organisation].CurrentMember, [ORG00]) 
  AND IS([Cost Centre].currentMember,[CC19000]) 
  AND [Amount Type].[AT1] < 0, [Amount Type].[AT1] * -1, Missing)'

 MEMBER [Amount Type].[AT6] 
 AS 
 'IIF (IS([Organisation].currentMember, [ORG00]) 
 AND IS([Cost Centre].currentMember,[CC19000]) 
 AND [Amount Type].[AT1] >= 0, [Amount Type].[AT1], Missing)'

INSERT 
 "([Amount Type].[AT3],[Scenario])" 
 TO 
 "([Allocations],[Beginning Balance Credit])"

 "([Amount Type].[AT4],[Scenario])" 
 TO 
 "([Allocations], [Beginning Balance Debit])"

 "([Amount Type].[AT5],[Scenario])" 
 TO "([Allocations],[Beginning Balance Debit],[ORG63],[CC19000])"

 "([Amount Type].[AT6],[Scenario])" 
 TO 
 "([Allocations],[Beginning Balance Credit],[ORG63],[CC19000])"
	 
INTO [Gl].[Basic]
FROM 
 (
   SELECT 
   {[Amount Type].[AT1],
    [Amount Type].[AT2],
    [Amount Type].[AT3],
    [Amount Type].[AT4],
    [Amount Type].[AT5],
    [Amount Type].[AT6]} 
   ON COLUMNS,
   {Crossjoin(
    [Acc19801].Children, 
     CrossJoin(
      Descendants(
       [ORGT],[Organisation].Levels(0)
      ),
      Descendants(
      [CCT],[Cost Centre].Levels(0)
      )
     )
    )} 
   ON ROWS
   FROM [Gl].[Basic]
   WHERE ([ANLT],[OUTT],[Scenario],[PUBT],[FRED],[Feb-08])
 );