OLAPWriteAliasToCube

Description:

This function returns an alias 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 an alias, that is extracted from a given alias group from a given dimension and this alias 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 alias value that is accessed in the function.

The function takes as a parameter the name of the database, 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.

Currently, the AliasToCube function works by double-clicking and picking a value. This feature allows you to type a new value (name) directly into the cell containing the function. It validates the name and writes to the cube intersection.

The function has the following syntax:

Syntax: OLAPWriteAliasToCube (database, dimension, aliasgroupname, aliasindex, 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 and which has the "Alias Group" from which the alias names will be extracted. / enter the cell reference for the dimension name that is to be accessed.
• AliasGroupName : Enter the name of an alias group that exists in the dimension that is referenced above, and which is the alias group from where you want to function to read values and return those values / alternatively enter a cell range reference which contains the name of this alias group as a value.
• AliasIndex : Enter the index number of any alias that belongs in the alias group name mentioned above. The alias index is used to identify which alias value has to be picked up from the list of alias names from the specified alias group.
• Cube : Enter the name of the cube which exists in this database mentioned above and within which the alias 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 alias group name, 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 drop down list.
• Choose the OLAPWriteAliasToCube 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 alias group name, the alias 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; B6 is the name of the dimension which has the alias group that is referenced by its name here which is "DataLoadCodes", 3 is the index of the alias name that is within the alias group accessed here, "test3" is the name of the cube to which the values are written back, and C4, and A18 are the members in the dimension of the "test3" 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 alias accessed to that intersection point. If the write back is successful then written value is the same value is returned by the function.

=OLAPWriteAliasToCube ($B$1, $B$6, "DataLoadCodes", 3, "test3", C4, A18)

 

The above formula returns a value = "11" which signifies that this is the value that is written successfully to a point which is an intersection of the members "Actual" (C4), and "BlanchettBlanchett Gifts Unlimited" (A18), which are members from the cube "Test3" that resides in the database "Acuity" (B1). The value that is written to this point, i.e.., the number 11is the alias value that is extracted from the "Acuity" database, from the dimension "AP_Vendor" (B6) and from the alias group "DataLoadCodes" and the alias lies at the index 3 within the alias group referenced above.