This function returns a value from the cube, based
on the parameters from k1 to kN.
The function takes as a parameter the name of the
database, the name of the cube within this database, and finally the
members that are to be accessed in order to determine the exact intersection
where to read from. These members can be one or a list of members
depending upon the dimensional structure of the cube.
The value that the function reads from a point in
the cube is the value that is returned and this value is accessed
keeping in mind the parameters given to the function. This value is
the result that this function returns.
The function has the following syntax:
Syntax: OLAPRead
(database, cube, member1, member2,…
, member n)
Explained below are the parameters that have to be
provided when building this function.
- Database : Enter the name of the database which
has the dimension that you want to access / enter the cell reference
which has the name of this Database.
- Cube : Enter the name of the cube which exists
in this database mentioned above that you wish to access /enter
the cell reference which has the name of such a dimension.
- Members (k1, k2, …k'n)
: Enter the name of the member from which a relationship will
be established to determine to point where the values have to
be read from in order to build the report / enter the cell reference
which has such a member as its value.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The database name, the cube name and the member
names must be referenced by their exact name and should exist
as such within the database.
- The member names should be chosen as such,
that they allow the function the to determine the exact intersection
points for which it should create the report.
- All the 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 OLAPDatabase 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 OLAPRead function from the list
of functions provided and click "Ok".
- When building the function enter the cell range
for the database name, the cube name, and enter the cell references
for the members, for all dimensions, which are page level members
as well as column and row level members, because this allows the
function to determine the exact intersection point from where
the value has to be read. Instead of entering the cell range the
values can be manually entered into the function. Also, the cube
name and the member names can be accessed into a cell through
the functions OLAPCube and OLAPMember respectively and this cell
can be referenced within the function.
- In the following example B1 is the cell that
holds the database name, B2 is the name of the cube, C3 and C4
are the page level members, that help determine the intersection
point, and B8 and A18 are the members on the rows and the columns
respectively that pinpoint the exact intersection from where this
function has to pull the values from.
=OLAPRead
($B$1, $B$2, $C$3, $C$4, $B$8, $A$18) |
The above formula returns a value = "84,666",
and this value is read from PowerOLAP and its based upon the parameters
supplied to the function that help it determine the intersection point
from where it has to read the value that is to be returned as the
result of the function. The value above exists in the database "Acuity"
(B1), for the cube "AP_Trans" (B2), and this value exists
at a point which is the intersection of all the members that are referenced
in this example, which is the member "Amount" (C3), the
member "Actual" (C4), the member "Qtr_1" (B8)
and the member Blanchett_BlanchettGifts Unlimited" (A18).These
members help determine the exact intersection point from where this
value is read and the same is returned as the result of the function.
