The new features for the Designer for Web Servcices are:

 

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

 

 Previously in DWS where you have a join in the query which forces you to explicitly name the table that the column is attached to, you     needed to alias the column to another name surrounded by tildes (~) for a parameter to appear in the grid. Now this is not necessary, and a grid parameter will appear if you just put the table.column notation in the query.

 

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

 

       Now when you enter a query name, connection name or web service name, this becomes automatically prefixed with Query_, Connection_ and Web_Service_. This is to prevent name clashes which used to cause compilation problems with the DWS, due to the fact that different objects within the web service were not allowed to have the same name. This feature can be turned off by un-checking the Auto Prefixing option in the Options menu, for backward compatibility with older project files.

 

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

 

        In web service generation, spaces are not allowed in the object names otherwise you get a compilation error when generating the web    service. Now spaces are replaced automatically with underscore characters, to make it easier to read the names of these objects and prevent the compilation errors.

 

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.

 

       ODBC/ OLEDB Parameterisation using ? or @ – This makes the web service more secure, because if you use the special characters ? or @ to  indicate an ODBC/OLEDB parameter, this forces DWS to parameterise the query at compilation time, rather than inserting values into the query text at run time. This prevents the possibility of malicious hackers using SQL injection on the web service, because the only values that can be inserted are just treated as variables, rather than parts of the sql query text. Note that ? or @ are interchangeable in default mode, to be compatible with Microsoft Access and SQL Server queries. It has been noted that the @ character is also useful for declaring variables in TSQL statements, and a recent change allows you to still use this functionality by unchecking the “Treat @ in queries as parameter prefix” box on the advanced features page in the Web Service tab.

 

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

 

       MDX Query support with Row Names: DWS has changed to allow additional columns to be returned in the result set containing the names for each row. This is used with MDX queries for OLAP cubes. Depending on the nature of the cube and the query, you may require 1 or more row name columns to be returned. If you have an MDX query that requires this functionality, please check the “Add N row name parameters” box at the bottom of the query window, and fill in N as the number of additional columns that you need. EG, you may have sliced the cube to show summaries of financial data by year and month along the time axis. This requires two extra columns to show the year and month row names, so in this case check the box and enter N as 2.

 

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

 

       Query interpreter finds output parameters in “On Columns” clause. For MDX queries that contain the “On Columns” clause, DWS now picks up the column names and puts them in the parameter grid as output parameters, so that this column data will be transferred through the web service.

 

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 DWS with less need for modification.

 

       Automatic parameter detection. Now tildes (~) are largely unnecessary in queries to identify the parameters. This is intended to allow users to cut and paste queries in to the query window from other sources, and get them working quickly with DWS. Please note that tildes can still be used for backward compatibility, and in cases where the syntax of a query is complex and the parameter needs to be stated explicitly.

 

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

 

       Pivot tables exposed as data sources from spreadsheets can now easily be picked up from the DWS query window. Just type in the column names as parameters in the query and this data will be imported from the pivot table.

 

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

 

       When you are executing a stored procedure from the DWS query window, there is no longer any need to explicitly name the output parameters. DWS detects the ‘EXEC’ command, and then the “Test” button will change to “Test/ Fetch Parameters”. When you press it to test your query, DWS will automatically detect the names and types of output parameters returning from the stored procedure and place them in the parameter grid.

 

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

 

DWS 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.