proCube’s bi-directional database reference formulas enable you to enter data into your proCube database through an Excel worksheet. By entering data into a worksheet dynamically connected to proCube, you can work as easily as you do when entering data through a proCube slice.
To enter data into an Excel-worksheet where Excel is connected to proCube:
Press F9 to calculate the slice.
Change to Create Excel Worksheet with OLAPTable
Generate your worksheet.
Open the worksheet in Excel.
Find the intersection of Quantity and March and enter 100 and press Enter.
Press F9 to recalculate the worksheet. This figure shows the updated Excel example worksheet:
Return to the proCube slice and press F9. Note that the slice has been completely updated.
Figure 2. Update in proCube to the Sales Accounts Months Slice
Note that all cells in the proCube grid have been updated according to the figure you entered into the Excel worksheet. Furthermore, if you intend to save the worksheet as an Excel file, select the File, Save (As) command in Excel.
Only values at the intersection of Detail members can be edited in the Excel worksheet (this also applies to Slices). Aggregate member values, as well as Detail members affected by Cube formula values, cannot be edited.
Drill Through Functions
The Drill Through button allows you to see the source of an Aggregate data value as the proCube intersection of dimensions. If you are using Xchange, it will also allow you to see the relational source of data.
To demonstrate the Drill Through Function from the Sales slice in Excel:
Click the Total Year Quantity cell refer to Sales_Accounts_Months_Slice_Generated_as_OLAPTable.
From the proCube Ribbon, click Drill Through.
Press F9 to update. The Drill Through Dialog opens.
Figure 3. Drill Through Dialog
By default, the dialog is set to perform a Drill Down, which provides lightening-fast access to Drill Through data because it only shows data points for members with one or more non-zero values in the multi-relational database.
In rare cases, you may want to include members that contain zeros in a Drill Through. In such cases, deselect Drill Down Only and select Drill Through to Relational Source, which shows all data points in the Drill Through, plus all members’ data that contain zeros in the database. Drill Through to Relational Source results in slower performance because proCube must read all records in a relational database, rather than only those members with non-zero data points.
Since we are not using Xchange at this time , we are unconnected to a relational database source, and the four checkboxes in the Options area are grayed out. If we were connected to Xchange, we would be able to Drill Through to see the relational transactional source of the data value.
This action automatically creates an additional Excel worksheet in our workbook from which you now can see the sources of the Total Year Quantity value listed.
Figure 4. Total Year Quantity value listed
The four columns labeled A – D across row 1 are the Dimensions where the Member labels are found (the Members are shown in row 2, A – D). The value we “drilled down on” is listed in E2. The Members that comprise that intersection value are listed beneath in A3:E9.
Each additional Drill Through action performed on a sliced worksheet creates additional worksheets.
You can now save and name the Excel worksheet as you normally would—and this worksheet will remain connected to the proCube database.
If you are planning to use the new Express Drill Through feature on an OLP file created with an earlier version of proCube, you need to completely rebuild your Xchange cubes to realize performance improvements.