This function reads and writes a point from an Analysis Services cube. You must specify the data-source, database and cube to read from. You must specify a member for each dimension in the cube. The point is editable and when changed, gets written back to Analysis Services. The ASReadWrite function allows the user to pull in values from any analysis service, connect to a cube in it, and selectively pull in the data that you require from a particular dimension and member and bring that into PowerOLAP. It also allows the user to write back values through any detail point, and these values actually get written back into Analysis Services. The cube in PowerOLAP is where the "Readable/Writeable" values are stored. This implies that once the ASREADWRITE function reads values from the source, then in order to access these values they need to be stored in a location and the location is a cube in PowerOLAP. The dimensions and their members in the cube in PowerOLAP must be an imitation of the dimensions and the members of the source cube in the source database (that are parameters to this function), existing within "Analysis Services" from where we are pulling in data.The ASReadWrite works just like the ASRead function for reading data and the formula structure, and most part of the syntax also remains the same. However what changes is the fact that with ASReadWrite a user can not only access values into PowerOLAP but once a connection is established, one can make appropriate and desired changes to the cube and actually send the data back to the source. So it works a little like the ReadWrite function already existing in PowerOLAP except that this function works from within PowerOLAP and it's aimed at writing back to Analysis Services. Although the cube in PowerOLAP must contain all the dimensions that exist in the source cube, however those dimensions need not contain all the members in the analysis services cube. You may create only those members within which you want to populate values, and display the same. These member names in which you pull in values, must match exactly with the member names in the source database and even a small typing error can be responsible for not pulling in values from the source cube because an exact match is completely necessary.
Syntax: ASREADWRITE (data-source, database, cube, member1, member2, …)
- Data-source : Specify the name of the server that is has the "Analysis Services" database on it or a data source name that maps to the source that has the analysis services database.
- Database : Specify the name of the database in "Analysis Services" which you want to connect to.
- Cube : Specify the name of a cube in the database mentioned above.
- Member1 : Specify the name of the member in the 1st dimension of this cube.
- Member2 : Specify the name of the member in the 2nd dimension of this cube.
- Member-n : Specify the name of the member in the nth dimension of this cube.
Remarks
- The name of the database, the cube and the member names supplied to the function must be a valid and should exist in the database and must be spelled accurately.
- The cube that is referenced above should be allowed access to from the "data source" supplied also as a parameter. The data-source must be valid and operational.
- All arguments are compulsory.
Example
Let us create a new database to illustrate this example. This database is named "AnalysisServicesinPO" and contains a cube called "ASReadWriteCube". The dimensions and their members in the "ASReadWriteCube" are an imitation of the dimensions and the members of the source cube in the source database within "Analysis Services" from where we are pulling in data. In this example we are pulling in data into our cube in PowerOLAP from the "FoodMart 2000" database in MS-Analysis Services. In this instance we are using the "Budget" cube that exists within this database. Therefore the cube "ASReadWriteCube" cube in PowerOLAP is a replica of the "Budget" cube in "FoodMart 2000".
.png)
The above formula populates the values into all the members of the "ASReadWriteCube" and it reads the members and their values through the "ASReadWrite" function which will not only read values but also allow you to write back from a detail level of this cube to the source cube in Analysis Services. In the example shown above, "ESCHER" is the name of the server which is the data source parameter for the function and it is where the "Analysis Service" is running. Also, "FoodMart 2000" is the name of the database on this data source from which the cubes are going to be accessed, "Budget" is the name of the cube in the "FoodMart 2000" database whose dimensions are accessed, and "Account", "Category", "Store", "Time" and "Measures" are the dimensions being accessed.
Within these dimensions, for "Measures" dimension the amount member is accessed here because it is the only measure, but for the rest of the dimensions, the formula uses a "Current" formula to access the currently selected member and dynamically populates values into the cube based on the selected member. The multiple parameters in the "ASReadWrite" function allow PowerOLAP to identify the exact data point /intersection in the analysis services cube from where the data has to be read.