SSIS with Spatial / Geospatial Data

Mind's biggest strength is it knows no barriers / boundaries and this is also mind's biggest weakness. This sounds highly philosophical, so let's go to something technical with the same philosophy. One of the area of data which knows no boundaries is Spatial data, as any point on this earth across geographical boundaries can be plotted with pin-point precision.

Like a salesman, when it comes to something new, my mind immediately starts selling the concept of how SSIS can be sold even in this side of technology and business. Before data lands down to SQL Server in geography or geometry data types, this data can be found in different formats which different applications generate or use. For example, Well Known Text ( WKT ), Well Known Binary ( WKB ), Keyhole Markup Language ( KML ), Geography Markup Language ( GML ), Data Geocoded in the form of latitudes and longitudes are all parent compounds of geography or geometry datatypes, which we eventually can convert to store or use in SQL Server. Also there can be a need for mutual exchange of data from SQL Server to other formats.

In these cases, apart from the CLR functions that can be used with spatial data types, SSIS has the possibility of playing a major role in crafting the conversions from one data type to another. Google maps still use KML and Microsoft Virtual Earth uses GML to the best of my knowledge. Think of the possibility where one needs a conversion directly from GML to KML without staging in SQL Server. In this case SSIS and SQL XML can definitely play a big role. SSRS has also got great support with maps which also needs spatial data to fuel itself.

And those who are curious to know how interesting can be exploring and working with spatial applications, just explore the GeoSpatial Data Generator application designed by Mike Ormond.

How to export SSIS Control Flow / Data Flow / Event Handlers to Word or PDF format

Have you ever come across the question which is the subject of this post ? This is one of the requirement that I have come across several times in my projects till date, and before revealing much about the scenario and answer to the same, I would like to request you to take a look at one of my earlier posts where I have described some of the feature shortcomings in BIDS and SSMS.

I approached one of the popular SQL Server websites (I would keep the name anonymous) to help me create awareness and gather public opinion about the needs of these features from the developer community, but I got a denial from their side. I got a suggestion from their side to request individual blogger to help me to create awareness. I have created a Connect item to request the same from Microsoft, but most of the developers would not be browsing connect digest everyday or even once a month to take a look at feedback / suggestions / bugs and vote for / against them.

After going through the above mentioned post, if you feel that it's a valid requirement, please vote the Connect item I have created here. If you have a blog and feel like supporting my request, please blog about it so that more attention from the community can be brought to request these features from Microsoft for BIDS and SSMS.

Now coming back to the answer of the question in subject, which is the reason for which you came to this post, just browse this SSIS Forum thread to read the scenario where this feature was required by a professional and my solution for the same.

SSIS , SSRS , Powerpivot , Sharepoint and .NET with SAP

Today I was going through an article featured on powerpivot-info.com, and I stumbled upon an interesting set of components offered by this company called "Theobald software". Before I go ahead with the entire story, a little background about SAP and why a MS BI geek would be interested in SAP. Every mature and informed professional in BI should be knowing that SAP is the world-class ERP solution for almost every kind of business and Germany can be considered as Mecca of SAP. Now like any Enterprise level products it provides an application level interface and also holds data within itself in a proprietary format. Entire business process workflow for almost any kind of business and any kind of process can be managed using this product, and the biggest USP of this product is that it comes with a proprietary programmable API called BAPI. I can write for an year about this product and still I would fall short, so I would cut this story short here and move ahead to how on earth I know about SAP and what a MS BI guy has to do with it.

I have been working since the past 1 year on a systems integration programme where I am involved in migrating data from a proprietary ERP application to SAP, and the tool we have been using is SSIS for data migration. It has been a very interesting, challenging and one of it's kind of experience as a Technical Lead as this is the first time I am working in such combination of technologies.

Coming back to where we started, this company offers components that can be used as a wrapper between SAP and Microsoft technologies like .NET, SSIS, SSRS, Sharepoint and Powerpivot. I have glided over the features that this product offers and based on my whatsoever little experience of working with SSIS and SAP, below are some points that should be considered before using these or any components while working with SSIS and SAP.

1) Business data in held in proprietary format in SAP, and any change triggers a workflow or a set of processes in SAP. It can be thought of an application equivalent of mainframe systems, and it would take you to be no less than a senior level business analyst or senior SAP functional consultant to change any data into SAP.

2) Based on point 1, it can be deemed that for other applications which may aspire to hook into SAP, like SSIS or SSRS, it can only be a read mode access and not a write mode access.

3) The components that this company provides, claims to have features that facilitate to read data directly from SAP tables. My experience has been that, first you need to have a broad knowledge about what these tables contain, as neither the names would be self-relevant nor it would be that easy to figure out relationships from any table like foreign-keys in a database. For example, in the Materials Management module would have following tables: MARA, MAKT, MBEW, MLTX and others. Even all the column names would be in German. A VC++ programmer would easily co-relate with these kind of nightmares.

4) Another feature that these components offers is of SAP queries, something like our stored procedures. Believe me, you would never require any reporting tool if you are using SAP. Almost any kind of reports can be created with any level of flexibility that can be imagined. To put just one of the fact to the table, just any single module of SAP comes with approximately 800 built-in different kind of reports which can be modified too. Also if the report is provided in Excel format, Excel itself can handle much of the graphical or pivoting features to a considerable extent.

5) If I consider the usage of mySAP portal (which can be thought of similar to Dashboards hosted on Sharepoint created using Performancepoint Dashboard Designer), master data, and BW Cubes which might need to be used and/or analyzed with another system that is hosted on a different platform like Sharepoint, in this case I see a good use of these components to create a BI solution on the top of SAP and any other ERP level product or any data models hosted in a different platform (including Microsoft). I recently read on a whitepaper that Panorama Novaview is able to hook into these SAP ERP Tables, and Microsoft SSIS also has a connector to read from SAP but we do not have any components out-of-box in SSIS / SSRS specially designed to work with SAP.

This company is German in my understanding, has all the German business partners, and claims to have 650 clients out of which half of them are german. But even if I consider the rest, it should be having 300+ clients who are using MS BI integration with SAP, and in my career till date I have worked and even heard of only a single project where SSIS is used with SAP. I really wonder how big is the BI Universe and how smaller is my knowledge !! :) These components are interesting and if you have even any brittle idea of a few terms of SAP, I suggest to check out these components and catalogue them in your list as this cannot be considered less than an MDX library available in the form of Excel Functions ( I should have said DAX in short ).

I plan to publish another post sometime after I recover from the above shock, to share how we use SSIS and SAP R/3 for data migration from a proprietary CRM application that is stored in Oracle Warehouse to SAP for all the modules like Materials Management, Customers, Vendors, Account Receivable, Accounts Payable, Human Resources with Parallel Payroll (the most complex module I have ever worked in my life), Cost Centres, WBS, Fixed Assets, Purchase Orders, GL Balances and many more.

SSAS + Excel Services + SSRS + Performancepoint Services = Powerpivot + Office 2010 + Panorama Novaview ?

The equation mentioned in the subject of this post seems to be something like a combination of keywords that one might throw at Google to find out some results. If you use google with some values with binary arithmetic operators, google might answer the result. But for the above equation, the only engine than can answer the result of the equation are the BI Engines of Microsoft and Panorama.

Panorama has come out with quite a handful of products or product features that works with powerpivot and microsoft office 2010. After going through the features that Novaview offers for leveraging the capabilities of Microsoft Powerpivot, I particularly feel interested with the Universal Data Connector capabilities of NovaView, as it can hook into a variety of data sources ranging from Excel to SAP ERP tables.

My equation might not be precise, but from my initial grazing on the Parorama's datasheets and whitepapers, I have summarized my understanding in a single equation. And as it is said that "Keep your friends close, and your enemies even closer", I like keeping a watch on other competitive (I mean to say Partnering) products. And if you adopt the same philosophy, check out these panorama's integration with microsoft business intelligence whitepapers from here, and one another great source of information that is more precisely banged on target is a post on Ella Maschiach's BI Blog.

Visio Services and Dashboarding : Visio Web Acces web part

In the Microsoft Business Intelligence eco-system, the entire or major presentation part of the business intelligence solution is hosted as of day on Sharepoint 2010. Performancepoint Services, Visio Services, Excel Services and Silverlight can be seen as major or crucial constituents of the dashboarding solution on the Sharepoint platform. As a solution provider, I believe that one needs to have a vision of overall picture of the eco-system than keeping the focal point on only one technology, as an entire solution can never be delivered on the back on a single technology, whether it may be SSIS / SSAS / SSRS / PPS or any other part of the Microsoft BI Solution Deck. By this I am not denying the fact that one needs to develop an expertise in at least one technology of choice to keep oneself in the front league of the business, but still having an idea of how the nuts and bolts fit together is very essential in my viewpoint.

The most unnoticed part in the MS BI eco-system is Visio Services, and some would even argue that it is not even a part of BI. But in my viewpoint, just SSIS, SSAS and SSRS do not complete the MS BI solution. Coming to the point now, we know that we can create data-driven visio diagrams which can be hosted on sharepoint 2010 and visio services enables viewing of the same over a browser page.

The point of focus of this post is a web-part in Sharepoint 2010 that is dedicated to Visio much like the Silverlight web part. This web-part is called "Visio Web Access" web part under the "Office Client Applications" category. When we create a dashboard in Sharepoint 2010, we can add this web part and host our data driven (or static) visio diagram (which should be hosted on sharepoint 2010) in this web part, and it contains all the properties specific to customize the viewing of this diagram. The most interesting part of this web part is that data driven visio diagrams hosted in this web part can be linked with other web-parts, so that context sensitive data can be presented on the dashboard. Read this post for a detailed tutorial on Visio Web Access web part.

The steps towards using this web part from a very higher level can be as below:

1) Create your dashboard using Performancepoint 2010 dashboard designer and deploy it on sharepoint.

2) On the dashboard hosting page, create a visio web-access web part and specify the location of the visio file (.vdw). BTW, this web part also facilitates display using Silverlight, if installed.

3) Link this web part to other scorecards on your dashboard, to make it context sensitive.

Please keep in view that above mentioned are my views, and derived from the features published on the documentation of Sharepoint 2010. I have not tried it out, because as of this draft, I have not got a chance to get my hands on the same. But sooner or later, I should be able to manage a hands-on experience on the same and extract more tried and tested solution recipes.