SQL Azure Reporting versus SSRS : Challenges in cloud based reporting

SQL Azure Reporting is soon expected to be available on Azure platform, and as the name suggests, it is expected to cater reporting out of SQL Azure. A nice comparison of cloud based SQL Azure reporting and on-premises SQL Server Reporting Services can be read from this page.

Nice part about this cloud based reporting is that one can author report from cloud and host it on the cloud. But when authoring is done on the cloud, the only data source available would be SQL Azure. This makes the scope quite limited for the use of such reports. Earlier too, it was possible to create reports out of SQL Azure databases using SSRS, and one of my popular articles on how to implement the same can be read from here. Instead of being a PaaS / SaaS solution, I see it just as an extension to SQL Azure feature set, something similar to export data functionality. Support for reporting from on-premise data using WCF / RIA services would have made this reporting solution much admirable.

It's also being heard on a few blogs, that one can now wash away their hands from maintaining the reporting infrastructure as reporting is available on cloud, but there are a lot of questions before this statement. In my views, there must be some exceptional reasons to implement a reporting infrastructure for a cloud based database. Instead of it, the best solution is a PaaS / SaaS reporting solution.

Probably it's too early to make any comments right now, and you can get more information from this PDC video. In summary the good news is that it's the first ray of hope to see BI related features and services on cloud. You can sign up for the SQL Azure Reporting CTP from here.

Windows Azure Market Place ( Data Market and App Market ) - Potential jobs for BI Tools

Windows Azure Market Place is a cloud based service offering on the Azure platform from Microsoft . For those who are completely unaware of Azure, I can say that I find it synonymous to Amazon Web Services in the making. Services available or to-be available on this platform can be divided in two parts: Data Market and App Market.

Data Market which was formerly know as Microsoft Codename "Dallas" is now commercially available, and this announcement was made very recently. Also many other significant announcements for the Windows Azure Market Place, which can be read from here. App Market which is another section of services related to application development and hosting would be commercially released, probably early next year.

My area of interest revolves around data market. Data Market being a platform of data / content providers is a potential future candidate to be considered as a vital data source on the cloud. Traditionally we were used to see data sources as only in-premise databases like SQL Server, Oracle, Teradata, DB2 and others. But with the advent of cloud, we also see SQL Azure as a data source today. In my views if this platform evolves considerably, there is a bright scope that this is the probable new data source on the cloud.

Also this opens up jobs for MS BI Tools, especially SSIS and SSRS. I definitely see a requirement to merge data from different data providers that one might have subscribed, and create reports which is facilitated by Excel / Excel Add-In presently, can be replaced by SSRS. Data access from this platform is facilitated through REST APIs or OData, and MS BI Tools lack this support. But by the time DataMarket gains adoption as well as maturity from partners and consumers, we can expect MS BI tools to provide support for the same. Powerpivot is already aligned by Microsoft to serve the data analysis needs. Interestingly Tableau which is one of the leaders in data analysis software with it's huge range of data visualizations, is one of the technology partners and is providing support for data analysis from DataMarket. Check out the announcement of integration between Tableau and DataMarket from here.

To have a first look at the DataMarket section of Windows Azure Market Place, check out this video.

Use of SQL Server 2008 R2 Express Edition with Microsoft and Non-Microsoft BI platforms

SQL Server Express Edition is one of the most admired databases for BI platforms. Before a couple of years, I would not have been able to make this statement, but through the due course of experiences of my career, I can make this statement quite confidently today. Non only on microsoft platform, but non-microsoft based BI solutions are quite happy to include express edition as one of the constituents of a BI solution for either housekeeping tasks or to catalyze data / data processing. Some of the interesting use of express editions are listed below.

1) Proof Of Concept development: During the POC phase, projects are least likely to procure licenses. One of the first benefits of express edition is that it's license free for use in development as well as production, and does not come with a time bomb in it. As far as programmability features are concerned, it comes with almost all the features that an enterprise edition contains. So if the requirement is just a database container, express edition is one of the best starters on the menu.

2) Test data store: Instead of using resources on the server with higher editions of sql server, express edition can be used as a warehouse to store test data. When the development team requires test data during the development phase, they can always synchronize their local instances with test data from an express edition which can be treated as a test data server. This express instance can be handed over to a business analyst who would generally prefer to own a database free of complexity.

3) Metadata provider: Often many legacy systems or systems like SAP or other CRM systems emit out data in text / CSV files. Now these files many times contain non-printable characters or other junk characters, and the issue occurs during the import process. As a part of structural testing, legacy data files can be imported into express instance which can be installed at the source. Importing full / sample data into express edition can provide concrete metadata structure to the data (which does not exist in text / csv files) as well as test the import process. This can be done right at the source free of cost with express edition, making ETL processes and data migration more smooth. Many non-microsoft platforms would consider express edition as a tempting choice for purposes similar to these.

4) Temporary Staging: SQL Server 2008 R2 express edition has the database size limit of 10GB. This is a reasonable size for considering express instance as a temporary staging server in your ETL solution to stage master and/or transactional data.

5) BI Housekeeping: A production / UAT / QA server DBA would not allow external applications to connect to these servers. A project might want to retain a repository of execution and performance data history, and express edition is one of the best choice for such purposes. One can use any number of analysis / reporting tools on this edition without any security concerns.

Above mentioned are some of the uses of express edition in a BI solution development environment. 10GB DB size, SSMS, almost full DB programmability features, and license free production use are the major features of express edition. Keeping these features in view, you can consider SQL Server Express edition to economically catalyze your BI solution development.

Data Source View versus View in Data Source for SSIS , SSAS and SSRS

There is a vast difference between data source views and views in data source. We all recognize data source views i.e. DSV as one of the means to means to implement UDM. But I am of the opinion that when views in data source are not feasible to implement, one should resort to using DSVs. By views in data source, I mean that if you are using SQL Server as your OLTP data source, create views in SQL Server instead of creating lot of named queries / calculated columns using DSVs. Some of the advantages of using views in SQL Server for ETL / Cube Design / Reporting are as below:

1) Control and Filter over metadata: Mostly in SSIS a lazy implementation involves directly reading all the columns from a table, and tons of warnings in the progress tab as most of those columns are never used. With views you can always place a filter over the required number of columns, adjust data type casting as per requirement, and manipulate the metadata as required.

2) SQL advantage: With views in your database, you have entire SQL programming power at your disposal providing you the ability to create simple to most complex calculations. Also conditional logic, default values, null checks and many other validation and configuration can be placed at this level.

3) Adaptable to change: In case if the structure of your tables (fact / dimension) in your relational data warehouse change, or the structure of your OLTP source tables have changed, views can shield your ETL / Cube data source as well as save the solution from any kind of changes which would be required in case of DSV. Views can even help you to craft your snowflake schema into a star schema.

4) Transparency and Maintainability: If a new developer steps-in to maintain and enhance your legacy solution, swimming in your DSV to figure out the calculations can be a big challenge in itself. On the other side, analyzing a SQL select query can be much easy to figure out the calculations than analyzing each calculated column / named query in a DSV. Also a view can be used as a part of metadata / dependency analysis with SQL Server or other database analysis tools.

I do not intend to mean that DSVs are useless and views are the best. But a right balance between views in a data source and data source views (DSVs) can make your solution more robust and developer friendly than blindly using DSVs.

Web based OLAP client for SSAS : Report Portal

In my views, OWC (Office Web Components) available in SSMS is by no means a professional OLAP browser. Excel is one of the widely used client for browsing OLAP cubes, but there are some limitations with it when one wants to consider it as an enterprise client, some of which are listed below:

1) It needs to be installed on every client workstation
2) Data visualizations available are more suited for operational reporting than analytical reporting
3) There are almost no features for collaboration i.e. sharing generated reports with other users on a common platform unless you use Office Web Apps or Sharepoint
4) No spatial data support either in consumption or presentation with Bing Maps / Google Maps

Actually Excel has the capability to use OLAP / SSAS cube as a data source, but that does not mean that this capability can be treated as the primary purpose of using Excel. In one of my posts, I mentioned about Analyzer which is a product from Strategy Companion. Recently I came across one another such product - Report Portal.

Zero footprint, SaaS, Cloud are some of the buzz words while selecting a technology / product for solution development, and Report Portal is also a zero footprint OLAP web client for SSAS. In my view, this product lacks that professional and sleek features that is expected from a web client. Documentation is quite poor, export formats of reports are limited, there is no mention of SaaS offering. But some of the noteworthy features of this product are as below:

1) Report portal has got very distinguished data visualizations, which are quite impressive from a data browsing point of view. You would not use these kind of data visualizations generally for reporting, but to develop analytical reports, these visualizations can be quite helpful for analysis. Check out the Pie-Chart Tree Report, which is one of the quite unique kind of data visualization for data analysis.

2) Collaboration and organization of the analyzed data, which can be saved in the form of reports, is considerably mature.

3) Metadata can be browsed very efficiently, and this product makes a very extensive use of XML/A ( XML for Analysis ). The browsing features are quite friendly to take away the pain of forming MDX queries.

If you use it as a tool to save your OLAP analysis i.e. the results that you gathered while browsing or analyzing OLAP data, and want to stick it in the form of reports on a collaborative platform, it's a nice web client with zero footprint installation ( as it's a web based solution ). But if you are looking out to use as a full fledged self-service enterprise reporting solution out of your OLAP, I would feel much more comfortable and confident in Analyzer than Report Portal.

A wise strategic move can be to use Report Portal when you are taking your baby steps in your OLAP reporting i.e. the phase when you are developing your data marts / data warehouse. This product could well support your OLAP browsing needs efficiently as well as facilitate reporting at the same time, keeping the pressure of immediate reporting from your evolving data warehouse, at bay. When the solution is in the next level, you can choose a SaaS reporting solution like Analyzer and retire Report Portal. Please keep in view, this always depends upon scenarios and it's not a generalized strategy. The key difference between Analyzer and Report Portal is that Analyzer is a reporting solution, and Report portal is a web client and reporting is one of it's features and not a primary function.