This function returns the number of members for the
cube, range and dimension in the current database. This function is
designed specifically to allow you to view the count for the members
that are included in a specified fact security rule. The desired member
count can be accessed by specifying the database and the cube where
the rule is written, the index of the rule written and finally the
index of the dimension in that rule from where the members are counted.
This implies that in a cube there can be one or more fact security
rules written and within those rules there can be one or more dimensions
that are referenced and within those dimensions there can be one or
more members for which the rule is defined. Therefore it is essential
to specify the index for all these criteria in order to reach the
exact point to access the desired count for the members, and this
number is the result returned by the function.
The function has the following syntax:
Syntax: OLAPFactSecurityMemberCount
(database, cube, rangeindex, dimensionindex)
Explained below are the parameters that have to be
provided when building this function.
- Database : Enter the name of the database which
has the cube you want to access within which the fact security
rule is written / 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 and is the cube within which
one or more fact security rule / rules may be written and are
to be accessed by this function / instead enter the cell reference
which has the name of such a cube.
- RangeIndex : Enter the index of the range /
fact security rule that is written within the cube mentioned above
and which is the rule that you want to access. The rules written
for fact security in a cube are accessed sequentially in the order
they are written from top to bottom, numbered from 1 to n.
- DimensionIndex : Enter the index of the dimension
that you want to access from within the fact security rule accessed
above. A fact security rule can reference one or more dimension
and the referenced dimensions within a rule,are sequentially numbered
from 1 to n, and they are numbered in the order in which they
are referenced.
Remarks
- If a connection is not established with a database
the result will be a #value.
- The cube and within that the indexed fact security
rule must exist within this database and it should be referenced
by the accurate and exact name.
- 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 OLAPFactSecurityMemberCount function
from the list of functions provided and click "Ok".
- When building the formula enter the cell range
for the database name and the cube name. If you don't want to
manually enter the parameter you can use another function called
OLAPCube in order to get the cube name from another Excel cell
and then it's possible to use that cell as a reference for a formula.
Also enter the index of the security rule within the cube that
is referenced above and for that rule enter the index of the dimension
that you want to access from which dimension the member names
will be counted.
- In the following example B1 is the cell that
holds the database name, B2 is the name of the cube, 1 is the
index of the fact security rule within that cube, 3 is the index
of the dimension in the fact security rule (1) that is to be accessed
from all the dimensions referenced therein, and the next number
1 is the index of the member in the dimension referenced above
(3).
OLAPFactSecurityMemberCount($B$1,
$B$2, 1, 1) |
The above formula returns a value = 3 which
signifies that in the database "Acuity" (B1), and within
that in the cube "AP_Trans" (B2), there is a fact security
rule existing at the index 1 and within that rule more than 1 dimensions
are accessed and the dimension this example is accessing at index
1 is "AP_Trans_Measures" from where this example is accessing
all the members that are listed in the rule and the count of all these
members is the value returned by this function which is the number
3.
