Create and define IDS task to Pull Fact Data from a Remote Cube down to a Local Cube

1.   First, open a PowerOLAP database (UsingPO.olp) located in your local machine.

2.   From the PowerOLAP ribbon, go to Data tab, then in the Data Transfer control group select Internet Data Services command.
This will show the Internet Data Services dialog box.

3.   Select the Cube that you want to insert the IDS task into (For this example, select Future Year Model).

4.   Click Add to begin configuring a web service source. This will show the Configure Source dialog box as shown below:

Define the configuration screen as follows:

5.   Name the project – something meaningful to the user e.g., Pull Data from Canada.

6.   Indicate the server name.
(It will most likely be a web address or IP address, or even a local server if the database is running on your machine.)

When specifying a PowerOLAP server that uses a different port number CLICK THIS link

Note: When specifying a PowerOLAP Server that uses a different port number refer to the topic Connecting to SCP running on the same machine as the IDS Web.

7.   If the connection is secure, check the Secure Connection checkbox.
This means that HTTP Secure sockets is turned on, on the PowerOLAP server you are accessing.

8.   To get a list of the databases on the specified server, select Refresh. Then select a database (UsingPO Canada)

Note: If it's a secure database, enter the Username and Password.

9.   After you have chosen and accessed your database, select the Refresh button next to the Cube drop-down box to show a list of Cubes that are available in the database you have selected.

10. Select the desired Cube (i.e., Future Year Canada) to import. The image below shows a completed Configuration Screen.

11. Select Next.

The Options dialog box gives various options to change how you want the data to be updated between the two databases and between the two Cubes. This is where you can decide whether to pull the data down to a local database or push the data up to the remote database. You can do both by creating multiple IDS tasks: one to push data and one to pull. If you want to update Fact Data, or get Fact Data from a remote Cube into a local Cube, the Options dialog box for an IDS task is where you control that.

The Options for an IDS Task include the following:

• Skip Zeros
  - When the data is pulled any intersection of data that is a zero value will not be pulled into the local database.
• Skip Aggregates
  - Any value that is an aggregate point in the remote database will not be pulled into the new Cube on the local database.
• Skip Calculated Values
  - Any value that is a calculated value via a PowerOLAP formula in the remote database will not be pulled into the new Cube.
• Limit Data from the Source Cube
  - This allows you to pick particular intersections of data that you want to bring back from the remote Cube. If this is unchecked, you pull the entire contents of the remote Cube into the local Cube.
• *Update Dimensions
  - Checking this box means that when the data is grabbed from the remote Cube, for each Dimension in the Cube that is being grabbed, you can update your local copy to have the exact same Members as the remote database.
• *Update Formulas/Dependencies/Persistent Calculations
  - Checking this box means that you want to grab any formulas, etc. that exist within the remote Cube.

Important


The options with * (see 'Update Dimensions' and 'Update Formulas/Dependencies/Persistent Calculations' options) are advanced features and should only be turned on if the user or administrator knows what they are doing)

12. Select Next.

Depending on what you want the task to do; PowerOLAP may prompt you for more information concerning your Dimensions, like where you want the data to go in the local Cube.

Since the remote Cube that we are pulling down into our local database does not contain a Regions dimension, we need to specify to what Region we want to pull the Fact Data into.

13. In the succeeding dialog box, select from the drop-down menu a Region that will be populated with the fact data you are pulling from the remote Cube. For this example, select Canada region.

14. Select Next again.

15. Configure Limited Members– If you selected the checkbox to configure limited Members, i.e., Limit Data from the Source Cube option, the dialog box to do so will appear at this time. This gives the option to determine what data elements you want to pull from the remote Cube.

There are three ways to specify what data you want to limit from your location:

• Members
  - This allows you to select a single or multiple Members that you want to pull.
• Subset
  - A user could choose a previously defined subset of data– for example, Quarter 1, which would encompass the Members January, February, and March.
• Data Point
  - Much like the dynamic look-up capability of PowerOLAP formulas, you can create a parameters Cube, or such a Cube where you can enter the name of a Member that you may want to bring back. This flexibility allows a user to modify the values that are pulled back using IDS without having to actually modify the IDS task itself. The value would have to be simply entered into a Cube and the IDS task would take advantage of that value. This makes it very easy for an administrator to configure several or multiple IDS tasks—all that need to transfer data from one intersection to another intersection. For example, you might have a Version dimension of a budget, and you keep adding versions to your Dimension. At the beginning of each budgeting cycle, you may want to transfer the data from your previous version into your next version.  (See IDS task video for a more detailed example)

16. Set up the options (as in the example, we selected to pull only the Actual data), then click Next.

17. The Summary dialog box will describe what exactly your IDS task has been designed to do. Select Finish to see your IDS task listed in the IDS dialog box.

Notes


Once the task is defined once, it does not need to be defined again within that database.

18. To execute the Task, select the task (i.e., Pull Data from Canada) then click Execute.

You will get a message box that tells you the task has been completed successfully.

19. Select Done.

Now you should be able to make a Slice and view the data that you just pulled and executed through IDS. As the values change in the remote database, you can re-execute this task and the new values will come in, or you can schedule the task to execute automatically.

Open your Future Year Model slice, and then click the Recalculate button. You will see that the fact data for the Canada region now reflects the data that you pulled from the remote Cube (note the highlighted intersections for Canada).

Please see related topic:

• IDS Performance Considerations