Generating web services for Xcelsius using Flynet Web Service Generator

Nov 7th, 2008
by David Lai.

In this post David Lai, a BI Consultant and the author of davidlai101.com explains how to generate Web Services using Flynet Web Service Generator and use the Web Services to build live Xcelsius Dashboards.

For those who are using Xcelsius but without Business Objects Enterprise, you will either have to grab data from the database using XML mapping or Webservices.  XML mapping not very flexible in terms of generating complicated queries so most people will go to the webservice route.  The only draw back with generating your own webservices is the time it takes to code or cost to hire a developer to build the web services.

One way to generate webservices quickly is to use the Flynet Web Service Generator.

Its quite easy to install and setup as long as you follow the instructions which are included with the installation. Flynet works simplest with IIS but for those using apache like me, you will need to follow the Apache Web Server instructions which is included in the Flynet help file.

Generating a Web Service
First you will want to generate your webservice and you can follow the interactive walk-through found here

The tutorial shows how to create a web service from simple queries, HOWEVER if you want to create queries that involve Aggregates, Case Statements, and Procedures, the code will be a little bit more complicated. The manual and tutorial doesn’t really teach you how to do these things so the first thing you might think is that it is not possible and just stop using it.

Here is how you create queries with Aggregates, Case Statements, and Procedures

Aggregate
If you want to perform a count query, you will need to enclose the row counted and use the as ~row~ otherwise flynet will throw and error

Case Statements
Case Statements are similar to the Count statements where the case section is enclosed in the brackets.

Procedures
Procedures need to have the text EXEC at the start of the Query SQL statement followed by the name of the Stored procedure.

Please note that you must include the — for the output parameters

Connect the webservice to the Xcelsius Dashboard
Finally you can connect the webservice to your Xcelsius Dashboard but before you do so you should be careful of values that get returned that are of type DATE. Apparently a T gets inserted into the date row and Excel will think that the field is a text field, thus any date functions on the date cells will screw up.

To fix this, in Excel just use the DATEVALUE(), LEFT() and RIGHT() functions to strip out the T into the new cells

To try out Flynet Viewer go to http://www.flynet.co.uk

Related Posts:

Posted in: Articles, Introduction, Utility, Xcelsius 2008.
Tagged: · ·

←
→

  • Pingback: QAAWS vs. Flynet Web Service Generator | Practical Business Intelligence()

  • Bridgett Knapp

    edt5wdbm2zgndnpq

  • Ranjit Chikhalkar

    Generating a webserive is one part however using it in Xcelsius is also a critical job. I could create webservice successfully but when I try to connect it to Xcelsius and link data to a graph it did not show the data.

  • Flynet web services gateway coming soon…..

  • Ranjit Chikhalkar says:

    Hi
    I’m getting below error in following query, can any one suggest whats the C# keyword I’m using and will prefixing it by @resolve it?

    Illegal name error
    —————————
    The query or one of the key names appears to be an invalid C# identifier or is a reserved word (keyword) in C#

    SELECT FACILITY_NAME, COMPLETION_USER_KEY, PRODUCT, PRODUCTION_DATE, SUM(IMP_VOLUME) as IMP_Volume
    FROM APPS.EU_EST_DAILY_DISPOSITIONS_DV
    WHERE ( DISPOSITION = ‘~pDisposition~’ ) AND ( FACILITY_USER_KEY LIKE ‘~pFacility~’ )
    AND ( PRODUCTION_DATE >= ‘~pDate1~’ ) AND ( PRODUCTION_DATE <= ‘~pDate2~’ )
    GROUP BY FACILITY_NAME, COMPLETION_USER_KEY, PRODUCT,
    PRODUCTION_DATE order by PRODUCTION_DATE

  • Here’s the new feature list for the WSGd 2010 pre Release from http://www.Flynetviewer.com.

    The new features in this version of Web Service Generator for Databases are:

    1. Allow table.column dot notation in query parameters and connection strings – without the need for tilde (~) aliasing.

    2. Prefix Query, Connection and WebService name – switchable autoprefix option – to prevent illegal name problems.

    3. Replace Spaces with underscores in Query, Connection and Webservice names – to prevent illegal item names.

    4. Escape special characters in item names using word replacement – to overcome illegal character in variable name problems.

    5. Tidy up item name checking on dialog closing for queries and connections to make it more user-friendly.

    6. Add support for ODBC/OLEDB parameterisation – using ‘?’ or ‘?parameterName’ notation – to avoid SQL injection risk.

    7. Add support for MDX queries for OLAP data cubes, with switchable RowName columns – to contain outputs from “On Rows” clause.

    8. Added interpretation capabilities for MDX queries (OLAP cubes) – finds output parameters in “On Columns” clause.

    9. Modified Microsoft Analysis Services data source connection type to work with standard connection parameters, for Data Cubes.

    10. Simplified interpretation for aliased column names, now Tildes (~) are unnecessary for the most part – this allows queries to be pasted into WSGd with less need for modification.

    11. Improved parameter interpretation now allows WSGd to function more effectively with spreadsheet pivot table data sources in Excel.

    12. Automatic output parameter fetching for stored procedures, on pressing the “Test” button in the query editor.

    13. Built-in backward compatibility with previous project files, to allow them to be updated with these new capabilities.

    14. Backward compatibility with previous queries for selects and stored procedure execution.

    15. Double clicking on query names in the query grid will now open the query editor.

    16. Double clicking on data source names in the data source grid will now open the data source editor

    Basically, this Beta version of WSGd 2010 contains new functionality for handling cube queries and query result sets, as well as better query interpretation and safeguards to prevent naming clash errors.

    To return the data for a cube result set properly, additional output parameters need to be added to the parameter grid. These are the switchable Row Name columns, and they hold the names of the rows or dimension member names returned from the cube query, commonly shown as the left most column(s) of MDX query result sets. The number of Row Name columns you will need to handle your result set depends on the number of dimensions you have specified in your ‘On Rows’ clause in your MDX query. Row name columns are added to the grid using the check box and RowName parameter box at the foot of the Query Window. (The simplest of MDX queries just require 1 Row Name column)

    Other output parameters for the MDX query should be picked up automatically from the ‘On Columns’ clause in your query.

    You will find that the parameter interpretation in this new version is greatly enhanced, with much less reliance on the need for Tilde characters (~), so in most situations you should be able to cut and paste existing queries and find that they will be parsed by the Query Window without need for modification.

    So to generate a web service from an OLAP cube, for example, set up a Microsoft Analysis Services data source, and add a query that you construct or paste in, with the appropriate number of Row Name columns, and you should be able to see in the test window your result set with the dimension members you specified in the ‘On Rows’ section leftmost, and the results from the ‘On Columns’ section to the right of the results grid. The columns all become output parameters when the web service is generated in the normal way.

  • “You cannot escape the responsibility of tomorrow by evading it today.” – Abraham Lincoln
    Demi Moore

  • sandeep manocha

    Hello,

    I have suceesfully created the web service using flynet viewer. But can I use it in xcelsius

  • sandeep manocha

    I have created the web service in Flynet and that is working fine. But can I use this web service in xcelsius. What URL do i need to give in Xcelsius

    • Hi Sandeep,
      Yes you can use the web service in Xcelsius.
      The URL that you give Xcelsius is whatever file your web service created is.
      IE: if you call your webservice webservice.xml then you would put webservice.xml in the url portion

  • Ramón

    Hi!!!

    I am using Flynet as well, is it possible to do a INSERT TO query instead of SELECT?

    I tried but I couldn’t.

    Thanks,

    • Hi Ramon,
      It is possible to use Insert statements or Update statements with Flynet Web Service Generator or Designer for Web Services.
      Please see my blog post in the Flynet Xcelsius Server User Blog on our website, under the heading “Using Designer for Web Services to write back data” at:
      http://www.flynetviewer.com/Public/Community/Blogs/FlynetXcelsiusServerUser/default.aspx
      You may need to check your insert statement syntax, but you can use the query test button to help you do that.
      Since we have made doing Insert statements easier in our most recent version, you may wish to download the latest version of our software. There is a registration link to get the download in this blog entry.
      If you need further assistance, please contact support@flynetviewer.com
      Kind regards,
      Jim Plenty