This function reads and writes a point from a PowerOLAP database on the specified server. You must specify the server, database and cube to read/write from. You must specify a member for each dimension in the cube. The point is editable and when changed, gets written back to server.
Important: The members must be in the exact order of the dimensions in the remote cube.
The DBReadWrite function allows the user to pull in values from a PowerOLAP database that is opened on a server to which the user is connected. This function then connects to a cube in the database specified that resides on a particular server, and then it selectively pulls in the data that you require from a particular dimension's member / members and brings this data into the currently opened PowerOLAP database. This function also allows the user to write back values through any detail point, and these values actually get written back to the PowerOLAP database on the PowerOLAP Server. The current cube in PowerOLAP is where the Readable/Writeable" values are stored. This implies that once the DBREADWRITE 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 current cube 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 in the database that resides on the PowerOLAP Server and is the source from where we are pulling in data. The DBReadWrite works just like the DBRead 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 DBReadWrite a user can not only access values into one PowerOLAP database from another, but also, 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 another database that resides externally on a server. Although the cube in PowerOLAP must contain all the dimensions that exist within 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: DBREADWRITE (server, database, cube, member1, member2,.…)
- Server : Specify the name of the PowerOLAP Server that has open the PowerOLAP database, from where this function will read values.
- Database : Specify the name of the PowerOLAP database residing on the server mentioned above, to which you want to connect.
- 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 server supplied also as a parameter.
- The Server name should be valid and spelled accurately as well as, it should have the PowerOLAP database opened on it.
- All arguments are compulsory and neither argument is optional.
Example
Let us create a new database to illustrate this example.
This database is named "CrossDBServicesinPO" and contains a cube called "DBReadWriteCube". The dimensions and their members in the "DBReadWriteCube" are an imitation of the dimensions and the members of the source cube in the source database that resides on the PowerOLAP Server that is also specified here as a parameter. In this case, for the example the PowerOLAP Server is called "Escher" and the database residing on it is called "HumanResources" and it is from here that we are pulling in data. In this example we are pulling in data into our cube in PowerOLAP from the above mentioned database and in this instance we are using the "HR" cube that exists within this database. Therefore the cube "DBReadWriteCube" in PowerOLAP is a replica of the "HR" cube in the other PowerOLAP database called "HumanResources".
.png)
This formula above reads values from the "HumanResources" database that resides on the server, namely, "Escher". The cube that is read from this database is the "HR" cube and for the member names, since we require all the values of all members to be pulled into this database in PowerOLAP therefore we make use of the current function, which ensures that the member name being read by the DBReadWrite function, is the currently selected member name, and when the selection changes the value is also updated. In this way the DBReadWrite function accesses the values from another PowerOLAP database. From a slice in PowerOLAP, when the user is at a detail member level, values can be written back to the source cube on the server and the same values will be updated and stored in the source database.