Creating live dashboards using QAAWS

Happy new year to everyone. As the first post for the year I wanted to put something that was simple and common. In the last couple of months I have run into several scenarios of customers asking me the best mechanism to create live dashboards in XCelsius. As you know the only way to create live dashboards in XCelsius is by using Web Services. While you can create web services on your own the hard way, with the BOE platform there is a very nifty utility called Query As A Web Service (QAAWS) which allows you to create web services very easily from the universe.

QAAWS is a web service generator. It uses the query builder to essentially to build a query and publish the query to the platform and makes it available as a web service. Here is how you can use it.

Create the Query and Publish to the platform

1. Launch QAAWS and create a new query. Give it a name that is user friendly.

2. You can set additional parameters such as authentication mode and timeout at this stage. Usually we would keep them as default.

3. As you can see in the steps the next step is to select a universe. Based on your security setting you will be presented with a list of universes that you can use for building the query. The process of selecting the universe and building the query is the same as you would do when using WEBI.

In my example I have purposefully chosen a query which has a prompted filter. I can drive this prompt using a drop down box or other selectors from the dashboard.

As you can see my query has 1 input parameter and 4 output parameters. Now all I need to do it click on the publish button to publish the webservice to the platform.

The URL shown above is the URL for the webservice and I can use this in XCelsius to build my dashboard.

Using the Query in XCelsius

In the data connections dialog of XCelsius you can add a new live office connection. Here you need to enter the URL that we got from QAAWS.

We also need to bind the cells to both the input and output parameters. For the input parameter I have bound it to cell C3 of the spreadsheet as shown below.

The same way you need to bound the output to your spreadsheet. The only thing to keep in mind is that if your query will return multiple rows of data you would need to select a range of cells bigger than the maximum possible range as shown below.

For debugging purposes you can also get the number of rows returned by the query and bind it to a cell as shown below.

And after all of this voila you have a live dashboard…