Drill-down in Xcelsius using BI Services and Webi

Drill-down in Xcelsius using BI Services and Webi

BI Services allows you to publish a Web Intelligence report block as a web service. You can then consume this web service in Dashboard Design (formerly Xcelsius) to display the data in your dashboard. For many purposes, this can be used as a replacement for Live Office.

So, why not just use Query as a Web Service? Good question. If QaaWS service suits your needs, then use QaaWS, but in my opinion there a two big benefits to using BI Services over QaaWS:

1. You can apply logic and aggregation to transform your raw data into a more usable data set, better matched for the intended use in your dashboard.
2. You can schedule the Webi report, so that when you open your dashboard, data is being fetched from the Webi cube, not directly from your data source, providing faster load times.

In the following examples, I'll create a simple report block in Webi showing sales by Country. I'll connect to this block in Dashboard Design, and enable drilling so that a user can drill down to see sales by State and City.
In a future example, I'll set up another report block that shows customer details for the selected city. In essence this can give the dashboard user access to hundreds of thousands of rows of data in the database without sacrificing response times.
Note: In the examples below, I’m using Microsoft’s AdventureWorks database. In whatever database you're using, just pick three dimensions in a hierarchy and one measure.
I'll be using BI4.0, but the steps are the same in XI3.1, the main difference being that in XI3.1 you use the Webi Rich Client. In BI4.0, BI Services has made it's way into BI Launch Pad (the new InfoView). I'll point out differences between BI4.0 and XI3.1 as I go.


Setting up the Webi report

1. Create a new Webi report with a simple block showing country and revenue. Since I'll be drilling down to State/Province and City/Suburb, I'll include those in my query as well. I'm doing this in the web client of BI4.0 (BI Launch Pad), if you're using XI3.1, follow these steps in the Web Intelligence Rich Client.
2. Right-click on the block border and select Publish as Web Service (or Publish Block in XI3.1).
   
3. The Publish Content window appears. Click Next.
4. The system will check to see if this block has already been published. Assuming it hasn't, click Next.
5. Enter a name for the report block within the web service. A single web service can contain references to more than one Webi block. So during this wizard you will both name the block, and the web service. I'll call the block RevenueByGeography.
   
6. Click Next.
7. Now you can create the web service that will contain a reference to this block. Click Create…
8. In a future post, I'm going to reuse this web service to publish a second block that is a list of customer orders, so I will name the web service CustomersAndGeography. You can use spaces if you like - they will be replaced with underscores - but it's a good habit not to.
   
   I'll stick with Enterprise authentication for this example.
9. Click OK.
10. Click OK again.
11. Click Finish.
12. On the left side of the screen, click the Web Service Publisher icon. In XI3.1 there is a BI Services tick box up in the top-right corner of the Web Intelligence Rich Client, tick that.
    
13. Select the web service that you've just created (note that the report block is represented as a child of the web service), and in the properties section down below, you should see the URL that has been created. This is the URL that Xcelsius will need to be able to find your web service.
14. It's a bit of a trick to select the URL. Click anywhere in the cell. You won't see a flashing cursor, but pretend like it's there to select the text: hit Home, the Shift+End, then CTRL+C to select and copy the text to the clipboard. Alternatively you can click on the ellipsis to the right of the URL which will launch a browser window, then select and copy the URL from the browser window.
15. Save the report, and schedule it to run once (or select Run Now in the CMC).

Consuming the Web Service in XcelsiusIn a production environment, your users would probably be accessing this dashboard via InfoView/BI Launch Pad, so the system would handle the authentication allowing the user to access the web service. For the the purposes of testing though, we'll hard code the login and password into the spreadsheet.

1. Setup the cells of your spreadsheet like so:
   
2. I've greyed out some cells where I will insert the data from my Webi report. I've allowed for 100 rows of data, this of course depends on your data set.
3. Press CTRL+M or click on Manage Connections to launch the Data Manager.
4. Add a new connection of type Web Service Query (Query as a Web Service). You may be tempted to use the Web Service Connection connection type, but don't. The Query as a Web Service connection type will handle authentication for you, a Web Service Connection won't.
5. Name the connection Geography Drill Down or something equally as useful.
6. Paste the web service URL into the WSDL URL box.
7. Click Import.
8. Xcelsius goes and has a look at the web service and works out the things that you can do with it (the 'Methods'). At the moment, your options will be Drill_RevenueByGeography and GetReportBlock_RevenueByGeography. You may notice that these both refer to the report block. As you add more blocks to your web service, they will show here in the list of available methods. Cool, right?
   
9. Leave Drill_RevenueByCategory selected.
10. Map the Login and Password objects (in the Input Values box) to the appropriate cells in your spreadsheet.
    
    
    Next, you'll set the drill path from, value and drillOperation properties. But first, it helps to stop and think about what happens when you drill on a report block in Webi. Let's say I have a list of countries, and have clicked on Australia. I'm actually telling Webi three different things.
    
    Firstly, I want to drill on the Country dimension, because I clicked on a country. Webi will work out that I want to drill down to 'State/Province' because I've defined this as a hierarchy in my universe.
    Secondly, I want to drill down, because I clicked on the name of a country.
    Thirdly, that I only want to see results for Australia once the report has drilled down.
    
    So now we need to send those three bits of information to this web service so it knows what to do, and what data to return.
11. In the Input Values box, in the drillPath folder, click from and map that to cell D3 and click OK.
    
    This is where the header of my table will go, so this cell is going to have the word Country in it to start with. And that's the dimension I'll want to drill on.
12. Select the value object and map it to cell D2 and click OK. You may have guessed it, but when a user clicks on the component in the dashboard, I'm going to have it take that value (e.g. the country 'Australia') and place it in D2.
13. Select the drillOperation object and map it to cell B4. This defines the direction of the drill.
    
    You can actually just type the word DOWN into the Read From box, but let's keep it consistent.
    
    OK, now the web service has all the information it needs to go away and fetch some data. Next you'll tell it where to stick that data when it comes back.
14. In the Output Values box, select the cell object within the table > row folder.
    
15. Map this to the range of cells where you want the data to go (not the two header cells).
    
16. In the Output Values box, select the cell object within the headers > row folder.
17. Map this to the two header cells.
    
18. Back in the Data Manager window, go to the Usage tab.
19. Tick the box next to Refresh Before Components Are Loaded.
20. Insert a loading message into cell B3, like so:
    

That's it for the connection setup. Now it's time to test that it's working and start adding components to your dashboard.


Adding Components to the Dashboard

1. Add a Spreadsheet component (in the Selectors category) to your canvas and map it's Display Data property to a range that covers all the cells you'd like to see for testing.
   
2. Your setup should now look like this, with a spreadsheet component reflecting what is showing in the spreadsheet.
   
3. Click Preview to check that the data is loading correctly. If everything has been setup correctly, you'll see something like this:
   

Note that when the connection first gets refreshed (when the dashboard loads) it doesn't actually drill down, it just fetches the base data, that is, the highest level in the hierarchy.
Next I'll add a List View component (from the Selectors category) to the canvas. I'll move quickly through these steps, assuming you're familiar with how to add components and set their properties. In short, I will:

• Map the List View component to the grey range of cells, including the header cells (D3:E103)
• Set the column widths to 400 and 100.
• Set the insertion type to Row.
• Set the source data to the data in D4:D103 (excluding the header cell).
• Set the destination to D2.
• Set the chart to Insert on Interaction Only.
• On the Behaviour tab, set the component to Ignore Blanks in Rows.

Next, we'll change the data connection so that it refreshes (drills) automatically when the user clicks on a row in the list view component, and add a second connection that will allow the user to drill up.

1. Open the Data Manager.
2. On the Usage tab of your connection, bind the Trigger Cell to D2, which is the cell that will be populated with a value when the user clicks on a row in the list view component.
3. Ensure When Value Changes is selected.
   
4. Add another connection, again, use the Web service query (Query as a Web Service) connection type.
5. Name it Drill Up
6. Repeat the steps above to import the same WSDL URL, again leaving the Method set to Drill_RevenueByGeography.
7. Map the login and password objects to B1 and B2, as before.
   
   And just like before, it's worth stopping and having a think about what happens when you drill up in Webi. If you're looking at a list of cities, and click on the drill up icon, you're telling Webi that you want to drill from the City dimension, up to the next dimension in the hierarchy (State/Province). You don't need to specify the value that you want to drill up on, Webi knows that because it knows the path you took when you were drilling down. So this time around, you only need to tell the web service the dimension you want to drill up from, and that you want to go UP!
8. Map the from value in the drillPath folder to D3, the same as before, this will show whatever dimension is currently displaying.
   
9. Select the value object in the drillPath folder. Now, you don't need to define any particular value here, but if you leave it blank the web service gets confused. So just type anything in the Read From box.
   
10. Select the drillOperation object and type UP into the Read From box.
    
11. In the Output Values section, map the table > row > cell and header > row > cell objects to the same ranges in the spreadsheet as you did for the first connection. That is, D4:E103 and D3:E3 respectively.
12. On the Usage tab, make sure that Refresh Before Components Are Loaded is not ticked. You don't want the connection to drill up until the user says so.
13. Close the Data Manager (handy hint, if you've got two monitors or one big one, you can leave the data manager window open and continue to work on your dashboard).
14. Drag a Connection Refresh Button (from the Web Connectivity category) onto the canvas.
    
15. Name it Drill Up.
16. Bind it to the Drill Up connection.
    

Click Preview to test your dashboard. The connection should refresh and a list of countries be populated into your list view. Click on a row in the list view component. As you click, the appropriate value will be copied up into cell D2. This will trigger the drill down connection to refresh again, drilling down on the country dimension, based on the value (country) in cell D2. The list view will re-populate. Click on another item. The same process happens. Click on the Drill Up button. The second query runs, connecting to the same web service, using the same method, but with the instruction to drill up. The output data is inserted into the same set of cells and the list view updates again.
That's all there is to it!

If you're having trouble with your BI Services, be sure to check out my tips on troubleshooting BI Services. 

- See more at: http://blog.davidg.com.au/2011/03/drill-down-in-xcelsius-using-bi.html#sthash.XWfea6DY.dpuf