SSRS Report prototypes using Google Charts

Contemplating and conceptualizing design of any application starts with almost no tools at hand. MS Office - Powerpoint and Excel, or similar products are the tools that remain available with the design and development teams for developing application screen design prototypes. MS Visio is generally used to develop wireframes. In an application / report centric design, users preference remains viewing reports blended with the web application itself. As the need for a mature prototype develops, html - css - javascript based prototype starts getting developed.

Report prototypes are generally developed as excel spreadsheets or raw reports exported to excel, which are evidently totally isolated and hard to visualize how they would gel with the hosting environment. Google charts provides excellent variety of visualizations. The best part about this visualizations are:

• Visualizations are interactive, but they don't use Silverlight. Interactive term is used very loosely but its a very valuable term. SSRS reports are rendered as an image file which is not interactive. But these visualizations have one or other form of user interaction feature available along with tooltips.
• These visualizations are generated using HTML5 so they are cross-browser as well as mobile device compatible. Also visualizations are drawn using SVG or VML.
• Embedding these visualization and populating it with data is mere couple of html and javascript tags. Visualizations are exposed as google javascript libraries. Include those libraries in your page, create objects from the exposed object model and add data in the form of a very simple array. And your report visual is ready.
• Visualization such as treemap / intensity map, motion charts etc are also available which missing in SSRS.
• These visualizations are exposed in the form of classes, and they also have event listeners. This means you can bind user interaction on this visualizations with your server side code too.

One might also think why not use these visualizations in production environments ? The main reason I would have resistance against using these in production, is that firstly these are provisioned for free on google infrastructure. So you can't commit any SLA with confidence to end-clients regarding performance. Next google publishes a deprecation policy that supports backward compatibility for 3 years. This means that once any visualization is classified as deprecated, after 3 years that would disappear from google charts. This is not acceptable in any serious production environments. If I compare this with Microsoft policies, any deprecated or even discontinued products like Proclarity / PPS 2007 have a support policy for 10 years. But of course these products are not free.

So I suggest that its one of the best tools to use to create great report prototypes that are almost closest to the actual reports. Below is a sample code to create a pie-chart.

SSRS reports on iPhone, iPad, Android, Windows Mobile for Mobile UI

Viewing applications on mobile devices might sound like a small problem statement, but the actual solution requirement is much broader than just resolution adjustment. Some of the major challenges involved in the solution architecture are:

1) Single codebase for the model and controller layer, and using the same for creating different view layers for different devices and platforms.

2) Whether to create a webapp optimized for devices or whether to create a native application that calls webservices / displays web content.

3) HTML5 is supported in different capacities by different browsers, and most of the microsoft frameworks do not emit HTML5 by default. In fact features like local storage is not supported below IE8+. Also its quite heavier to use as the data exchange format for devices, compared to JSON.

4) Whichever framework is used, cross-browser compatibility is always an implicit / explicit business mandate.

5) Using same navigation design, for different sized devices like iPhone, iPad, Tablets and Desktops, would not be admired by users from a usability and user experience perspective.

6) REST based services are more faster and lighter to use compared to WCF based webservices. But WCF has got a very wide support, features and integration with .NET.

Below are some pointers that can be kept in mind while designing the solution / technology architecture:

1) HTML5 is supported by most modern browsers used on different devices. Creating a web application with HTML5, CSS3 and JavaScript is the most advisable step if you are completely inexperienced in mobile application development.

2) Web-based frameworks like jQueryMobile, Sencha, and Dojo can leverage the existing web based SDKs / codebase to build more sharper mobile applications in faster, easier and efficient manner. These frameworks have built-in libraries to use REST and JSON too for client-server communication. Even if web application is converted native application in the future, http/rest/json based communication protocol is supported by platforms like android, iOS and others.

3) To take your web application development framework to the next level, use frameworks like appMobi, Appcelerator, PhoneGap, ApplicationCraft and others to build native applications using JavaScript.

4) More about how to build iOS application from scratch can be read from here.

I have implemented architectures, where we create .NET user controls that makes programmatic calls to SSRS webservice for reports execution. The HTML output returned by SSRS report is collected and rendered in the control. These controls are hosted in .NET pages, which are hosted on Sharepoint. Finally when server leaves control, entire web content gets transmitted as HTML. By introducing HTML5 conversion wrappers at different layers depending upon the design of the solution, not only SSRS reports but any web application can be optimized for mobile devices.

There is lot more to consider like performance, navigation design, user experience, local data storage and others such points. Feel free to share your thoughts and experiences by commenting on this post.

Hadoop tools for SSIS, SSRS and SSAS like Integration, Reporting and Analytics

Data hosting, processing and reporting is dramatically changing on a variety of extremely different platforms than ever. With the emergence of NoSQL and Big Data, systems such as Hadoop host unimaginable volumes of data. Google is soon to hit 1 Billion Android device activations. US and China collectively contributes to almost 300 million iOS + Android activations. Sourcing data from systems like Hadoop, mashing it up with relational data sources and provisioning reporting and analytics on the most aggressively growing platforms like Android and iOS is not an easy job, leave apart the complexity, cost and skills involved in the process.

Recently I have seen quite a couple of SQL Server and MS BI related blogs writing about how to write code for HBase, Pig and for other similar sources. Industry matures in terms of developer productivity and user friendliness much aggressively than one knows. Talend - an open source provider of tools for managing Big data, provides a tool called Talend Open Studio for Big Data. Its a GUI based data integration tool like SSIS. Behind the scenes this tool generates code for Hadoop Distributed File System (HDFS), Pig, Hbase, Sqoop and Hive. This kind of tools really take Hadoop and Big Data to a extremely wide user-base.

After you have the ways to build a high-way to a mountain of data-source, the immediate need is to make meaning of these data. One of the front-runners of data visualization and analytics, Tableau, provides way to create ad-hoc visualizations from extracts of data from Hadoop clusters or straight live from the Hadoop clusters. Creating visualization from in-memory data and staging extract of data from Hadoop clusters into relational databases and creating visualizations from the same; both are facilitated by Tableau.

Other analytics vendor like Snaplogic and Pentaho also provides tools for operating with Hadoop clusters, which does not require developers to write code. Microsoft has an integrated platform for integration, reporting and analytics (in-memory/olap) and an IDE like SSDS (formerly BIDS).

If tools similar to Talend and Tableau are integrated into SSIS, SSAS, SSRS, DB Engine and SSDT, then Microsoft is one of the best positioned leaders to take Hadoop to a wide audience in their main-stream business. When platforms like Azure Data Market, Data Quality Services, Master Data Management, StreamInsight, Sharepoint etc join hands with tool and technology support integrated with SQL Sever, it would be an unmatched way to extract intelligence out of Hadoop. Connectors for Hadoop has been the first baby step towards this area. Still lot of maturity in this area is awaited.

Till then look out for existing leaders in this area like Cloudera, MapR, Hortonworks, Apache and GreenPlum for Hadoop distributions and implementation. And for Hadoop tools, software vendors like Talend, Tableau, SnapLogic and Pentaho can provide the required toolset. 

iOS integration with MS BI Solution Architecture

In my career experience, reporting has been the biggest piece of any solution. Mobile applications owns a large stake these days as an end-client application. Web is thought to be the ultimate platform for exposing your applications, but native applications that sits on mobile computing platforms like Android and iOS are huge stakeholders in the reporting consumers ecosystem. Benefits of web applications versus mobile platform native applications is a huge topic in itself, and demands a dedicated post.

As an architect, recently I have been given the helm of delivering an MS BI solution. The application has to be delivered as an iOS / OS X native application and the end clients have iPad and this application should be available as a native application on the same. Generally professionals with an architect cap, try to escape from the intrinsics of technology but in most of the cases I have seen that such professionals are not the dark horses of this game. If you give an attitude that I am an Architect (thou not an enterprise architect but just architect of your 2-3 applications) so I don't know anything about technology stack and nor can I analyze the skeleton of different technologies, then any business would want such professionals only on contract for the shortest time possible.

If you understand the entire different kinds of technology stack skeleton, you would be able to integrate your solution with joints of steel, and that is your job as an architect. Quick points that an architect would be interested in knowing about iOS world are listed below, which would help any architect to get a quick launchpad about iOS world of native applications. 

1) If you have made up mind to develop web applications that can target a variety of mobile devices, you can use native wrapper classes using frameworks like Adobe PhoneGap.

2) App Store and iTunes are great examples of native applications displaying web content inside it.

3) XCode is the SDK from Apple used to develop applications for iOS platform.

4) Most of the iOS applications are implemented using MVC design pattern.

5) Objective-C is the programming language used to develop applications, and its quite similar to C# too.

6) Interface Builder is the IDE that is used to develop application user interface and associate code with the UI. A simulator designer is provided inside this IDE.

7) Automatic Reference Counting (ARC) is the garbage collection mechanism of the iOS platform.

8) TableViews can be thought of synonymous to .NET configurable data grids.

9) Depending upon the complexity of the screen there are different kind of projects template available in XCode. For single screen apps one can use project types like "Utility Application" and for more complex apps that require different screens can use "Tabbed Application".

10) Different applications are targeted for different devices like iPhone and iPad. "Master-Detail Application" is a project type that would work seamlessly on iPhone as well as iPad.

11) Its possible to embed web content inside the application. UIWebView class would be a quick starting point to begin your base technical framework.

12) View Controllers can be considered synonymous to specialized controls that are aimed at specific tasks.

13) API to make calls to webservices synchronously and asynchronously is available in XCode SDK.

14) JSON (JavaScript Object Notation) is advisable to use for data exchange instead of XML. JSON is data exchange format, that is light-weighted, partly human readable, and consumes almost half the bandwidth compared to XML for the same size.

15) SQLite is the database management library and also supported by iOS SDK to store data on the device. Apple Mail itself uses the same.

16) iPad screen have 1024 * 768 pixel resolution at 132 pixels per inch, which is a good screen resolution to develop interactive applications.

17) iOS apps can use iCloud to enable different instances of their application to interchange data. Entire user data that sits in the "Documents" directory of the device, is backed up on iCloud daily.

18) iCloud has two types of storage: key-value storage for small amounts of data and document storage for larger volumes of data.

19) iOS applications are packaged as iOS App Store Package (.ipa) file. For internal beta distribution, applications can be distributed via ad-hoc method or ad-hoc (OTA) over-the-air method. Others third-party methods like TestFlight and BetaBuilder for iOS can also be used. Generally enterprises develop internal Enterprise Application Catalog (EAC) for mobile application distributions for company provided devices to download and install these applications. So this is also a good medium to publish your mobile applications.

20) Finally you have to submit your application to App Store team, if you intend to publish your application for iOS devices globally. It goes through a review and approval process from the same team, and they have their own set of standards for approving the application.

21) Important Apple Websites:

• iOS Dev Center
• iOS Provisioning Portal
• iTunes Connect
• Developer Support Center
• App Store Resource Center 

Excel 2013 BI versus SSRS 2012

1) Excel 2013 has PowerPivot 2012 integrated / inbuilt into it. This means access to vertipaq engine, data processing, and dashboard related benefits. Powerpivot 2012 is able to harness the power of Windows Azure Data Market too. More on the same can be read from here.

2) Excel 2013 had PowerView integrated / inbuilt into it. This means all the reporting related benefits, visualizations like charts - graphs - bing maps, KPIs and dashboarding and other dashboard related components like hierarchies etc. PowerView is designed to interpret DAX queries on BISM model, and integration with Excel means that now Excel is even more capable of reporting BISM data models than PowerView itself. More on the same can be read from here.

3) Excel 2013 can connect to Hadoop on Azure using Hive ODBC Driver. This means excel can exploit data from cloud from related data stored in Azure databases as well as big data stores like Hadoop. More on the same can be read from here.

4) Excel 2013 facilitates sourcing media and other rich content from the web using it's newly introduced Content app and Task pane app. This means very rich integration using a relatively least complex tool compared to other self service tools available in the industry. More on this can be read from here.

5) Excel 2013 ships with some great advancements in visualizations. Users are not expected to be very deeply insighted into reports development, and components that can even make suggestions on the kind of reports suited for the kind of data they are available. More on this can be read from here.

6) Most of the applications provide features to export data into excel, which means any kind of data can be easily imported into excel. Also most of the applications provide plug-in for Excel to provide deeper integration with excel to make the data exchange process easier.

7) Excel 2013 now supports decoupled PivotTable and PivotCharts, that means better representation of trend analysis. And even Excel Services supports this feature. More on this can be read from here.

This list is not an exhaustive list of features, and I am sure there are many more features worth listing. Above listed are just those that I could remember quickly. I am not saying SSRS is a weak tool by any means and on top of that its a server line of technology and integrated with SQL Server. It has great visualizations and available in the form of service which is suitable from a scalability and architecture standpoint.

But clients with

• lots of data stored in variety of data stores
• limited funds and even limited time and patience to wait for IT to facilitate their analytical needs
• totally resistant to learn another tool claiming to be their analytics angel
• and seeking to retain the power of determining the landscape of analytics in their own hands

would definitely find Excel 2013 as their BI magnet.

If you have more Excel 2013 BI features worth listing, feel free to comment the same on this post.

Excel 2013 Visualizations with SSRS 2012 Visualizations

I see lot of data management and exploration giants busy in building bridges with big data territory driven by Hadoop. But once the mashed query results are out of heterogeneous data stores made up of relational as well as big data, humongous amount of data would be returned. Tabular or even pivotal form of data representation using grids goes almost out of question. Perfect data representation becomes even more important than ever.

In MS BI world, SSRS 2008 R2 came with a lot of visual enhancements in terms of enhancements in visualizations. But SSRS 2012 came with PowerView only in this area. I see improvements in the reporting area from a developer productivity standpoint as graphs and charts comes with granular fine tuning capabilities. But a reporting product should be mature enough to direct its users to build the right kind of reporting.

Generally a reporting team builds a reporting solution with unsuited types of visualizations for data sets. This builds perception of the user that the underlying tools / technology is incapable of delivering the intelligence in an easy manner what they need for their business. As I often say most report developers or even reporting teams don't know what graph is suitable for the kind of data they need to analyze. A component that analyzes data and suggests a visualization is almost inevitable for any reporting product to ensure that the product is getting used in correctly, which is one of the best means to increase adoption and popularity of the tool.

Excel 2013 comes with a "Chart Recommendations" functionality which is unfortunately missing in SSRS 2012. Combo Charts feature lets you combine any two combination of charts or graphs which can be very exciting. Image a Pie-chart with bar-chart in each pie !! Data labels can be as flexible as any autoshapes that you see in a word document. More about Excel 2013 Charting enhancements can be read from here.

SSRS reports can be rendered inside Excel worksheets too through a RS command call. Instead of exposing SSRS reports through reports manager, I would suggest exposing reports by wrapping it up inside Excel, where you could add more value to reports and add better UI, sometime even better than SSRS itself too.

If someone from Microsoft is reading this, I would suggest SSRS team should collaborate with DataViz team of Excel and port those newly developed charting capabilities into SSRS and release it as a cumulative update. SSRS is already bleeding enough due to its age-old parameter toolbar !

Architecture Design Tools for MS BI

I got married last month, just returned from my honeymoon and catching up with the workload and routine life. Visual Studio 2012 got RTM and can be downloaded from here. Some of the new enhancements introduced in VS 2012 caught my attention and I find it really impressive.

One of the famous tools for architecture design modeling has been Enterprise Architect. Generally the gap between an architect and design (physical and/or logical) and development team is created by the tooling platform they use for their own set of works. Out-of-box you don't find an integrated platform that bridges deliverables of an

• Architect i.e. Layered Architecture Diagram(s)
• Design Team i.e. Class / Entity / Workflow Diagram(s)
• Development Team i.e. Code modules

Lineage tracking across these artifacts using an integarted tool can keep the solution totally in place. VS 2012 diagramming features provides exactly the same. Check out the following videos to get a quick overview of those features.

1) Visual Studio Premium and Ultimate 2012: Improving architecture through modeling

2) Visual Studio Ultimate 2012: Using layer diagrams to design and validate your architecture

3) Visual Studio Premium and Ultimate 2012: Understand your code dependencies through visualization

Only thing that feels disappointing is the extent to which these can be applied in the database / MS BI world. Once you move out of the application world and enter the world of data, I wish same could be applied to data warehouse architecture designs. As of now these features can be used to a limited scope for DW architecture blueprints.

It depends upon the eye and mind that knows how to use a piece of paper as a disposable glass !!