Consuming XML in Xcelsius

A few weeks ago, Chris Greer discussed a handy way to get data from a Crystal Report (universe, stored procedure, flat file, etc.) to a nice, clean XML file. In this post I want to explore this concept a little bit further to illustrate how this approach to scheduled XML creation can significantly enhance the performance of Xcelsius dashboards.

Where we’ve been…

• Created a Crystal Report that got data
• Created a report header and footer that provided the enclosing elements for the XML
• Created a detail section with variables that created well-formed XML

Xcelsius is great at having a bunch of ways to consume data sources. We’ve all seen great demos with dashboards that are very quick. However, once connected to SAP BusinessObjects Enterprise, performance may suffer a bit as it makes connections to authenticate, get Live Office components, or connect to Query as a Web Service (QaaWS). XML as a data source, stored on an internal or external web server, provides a very fast data consumption approach.

In an approach similar to that of Part 1 of this series, I’m going to create a Crystal Report that retrieves data from a universe, sets up some enclosing elements in the report header and footer, and uses the detail to construct the main body of the XML document.

Xcelsius requires a very specific schema in order to connect to an XML source to consume it. The format (with example rows) required is as follows:

Breaking this down a little bit further:

• The opening element. No need to specify any schemas or DTDs here.
• A container that specifies a name for the range of data being exposed. The X will represent a named range to be used within Xcelsius.
• Signifies a row of columnar data.
• The specific “cell” of data within each row.

Don’t forget to close each tag!

It’s a small adjustment at first, but as soon as you grasp how this data is organized for Xcelsius, it is simple to implement going first. The XML simply defines where a row starts and ends, and each individual column of data that is contained within a row.

A few “gotchas” to remember to when constructing XML for Xcelsius:

• Nulls aren’t going to play way. It is best to use formulas on each field to determine if it is null, and if so, simply put an empty column element:
• If you intend to do calculations within your model with numeric data returned, be sure to use the ToText function to strip out the commas that Crystal Reports will put in on a numeric piece of data. This will ensure the XML is treated like a number and not a character.
• I like to format my dates within the Crystal Report in a formula…but that might be just me.
• Consider putting one row in the report header that defines pieces of data that have been included within the report.

Get it into Xcelsius

With XML in tow, and published to a web server accessible to your Xcelsius users, begin by created a new Xcelsius connection using the Data Manager. XML Data as a connection type will give us that light-weight connectivity we are looking for to make sure the dashboard is very responsive.

 

Give the connection the basic parameters to start, including a connection name (no spaces). Ensure the XML Data URL points to the web address for the published XML. On the Usage tab, ensure this connection is set to refresh on load, or on other appropriate triggers.

Note: It is important that the web administrator ensures that XML files on this server are set to not cache.

 

For the newly created XML connection, check the box to Enable Load, and click the “+” button to add a named range for inclusion within the dashboard. The Name property must match the named range created within the tag in the XML file.

Click the Import Named Ranges button to assign the XML data read in to a cell range in the model.

 

In my model, I created a header row so I could be clear what cells the XML was being written in to.

In addition note that row number 2 will also have this same data coming from the XML, and shouldn’t be used within the dashboard unless you want to utilize as labels.

 

Now, create dashboard coolness as required based on that same range of data.

So, from data to Crystal Reports, and from Crystal Reports to Xcelsius. Consuming data in an XML file is really really fast and Crystal Reports provides a great mechanism for leveraging an existing semantic layer to get it there.