This function returns the member name if successfully
written to a cube. This function is designed specifically to allow
you to write back to a detail level point in a cube, based on specified
parameters that are provided to the function to help it determine
the exact intersection where to write back the value. The value to
be written to the cube is a member name, that is extracted from a
given dimension and this member value is then written to the cube.
The function returns back to excel the same value that is written
to the database which is indeed the member value that is accessed
in the function.
This function is used for referencing purposes.
The function takes as a parameter the name of the
database, the name of the dimension, memberIndex, the name of the
cube, the names of the members for each dimension within the cube
to ascertain a specific point to write back to and finally the value
that has to be written to that point.
The function has the following syntax:
Syntax: OLAPWriteMemberToCube
(database, dimension, memberIndex, cube, value/member1, value/ member2
…)
Explained below are the parameters that have to be
provided when building this function.
- Database : Enter the name of the database that
you want to access / enter the cell reference which has the name
of this Database.
- Dimension : Enter the name of the dimension
that you want to access which exists in the database referenced
above. / enter the cell reference for the dimension name that
is to be accessed.
- memberIndex : Enter the index number of any
dimension members that belongs in the dimension mentioned above.
- Cube : Enter the name of the cube which exists
in this database mentioned above and within which the member accessed
above will be written as a data point value /enter the cell reference
which has the name of such a cube.
- Member1 to n : Enter the names of the members
from each dimension of the above mentioned cube, so as to determine
an intersection point within the cube where the function has to
write back a value / enter the cell reference or multiple cell
references that have the names of the members as a value, and
these are the members that make up the intersection point in the
cube.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The dimension name, the cube name and the member
names must exist within this database and it should be referenced
by the accurate and exact name.
- All arguments are compulsory and neither argument
is optional.
Example
This function may be easier to understand with an
example.
- In order to build this function with more ease,
open the specific database and bring a slice from that database
into Excel. In this way you will have connected to PowerOLAP because
you now have the OLAPOpen formula automatically on your sheet
when you bring a slice of the cube into Excel.
- In Excel, use the menu: Insert -> Function
and select PowerOLAP from the category drop down list.
- Choose the OLAPWriteMemberToCube function from
the list of functions provided and click "Ok". When
building the function enter the cell range for the database name,
the dimension name, the member index, the cube name and the member
names to determine the exact point of write back. If you don't
want to manually enter the cube name or the member names you can
use another function OLAPCube and OLAPMember respectively in order
to get this value and then use that cell as a reference for a
formula.
- In the following example B1 is the cell that
holds the database name which is "UsingPO"; B6 is the
name of the dimension which happens to be called "Months",
which has the member we will write into the cube, 8 is the index
of the member name that is within the dimension, "Current
Year Model" is the name of the cube to which the values are
written back, and C3, C4, B8 and A9 are the members in the dimension
of the "Current Year Model" cube which helps define
the intersection point in the cube's dimensions where the value
has to be written back to. The function determines the exact intersection
where it needs to write a value to, and then it writes the member
accessed to that intersection point. If the write back is successful
then written value is the same value that is returned by the function.
=OLAPWriteMemberToCube
($B$1, $B$6, 8,
"Current Year Model", $C$3, $C$4, $B$8, $A$9) |
The above formula returns a value = "August",
August being the member with the index number of 8 as per the physical
order of the members in the member list of the Months dimension. You
can also double click on that cell with the OLAPWriteMemberToCube
function, and then select any other member that you want to be written
back to the cube from the Select A Member dialog box that appears
and then click OK. What now happens is that "August" will
be written to the data point intersecting at members "Actual"
(C3), "Sales" (C4), "Canada" (B8) and "January"
(A9) for the cube "Current Year Model" that resides in the
database "UsingPO". The return value of "August"
is actually extracted from the "Months" (B6) dimension and
for the alias that lies at the index 8 within the Months dimension
member list of the same "UsingPO" database.
