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.

SQL Server Modeling Services ( Oslo ) : Buddy of Application under the label of Database

In the history of mankind, time in divided in two parts : AD and BC, and my time in my career history is divided between .NET (BC) and Business Intelligence (BC + AD). Coming down to the language that humans can understand, I mean to say that I am a child of .NET and SQL Server, so I see any of these technologies from unbiased eyes. Apparently from the title, you can make out the topic of this post is SQL Server Modeling Services.

It was known as Microsoft Codename "Oslo", which is now known as SQL Server Modeling Services. The Nov CTP Release 2 of the same has got released a couple of days back and can be downloaded from here. There are majorly two tools released in this CTP : Quadrant and Intellipad and one major language "M" ( Microsoft Codename M ). Looking at the name and the features it offers, it seems like this package is more intended towards the benefit of the .NET community, but still SQL Server also has got a share of it. It has got quite some level of dependency on .NET 4.0 RC and SQL Server 2008. "M" is a quite interesting language to check out as it can generate T-SQL code from the so-called modeling language, which looks more like a C# code ( bad news for SQL Developers is that vice-versa is not possible). To me it looks like this is where Microsoft has created a hybrid of .NET programmers (C# coders in specific) and SQL Developers.

If you have SQL Server 2008 and VS 2010 installed, it would not be a bad idea to install these tools and check out the use of the same. Quadrant is something of the flavor of Toad for Data Analysts from a functionality viewpoint, but limited to SQL Server 2008 databases only. I am of the opinion that one should at least give a try to these tools, if not to the language "M".

And finally about the comment in the topic of this post, I think that though it's called SQL Server Modeling Services, it's more inclined toward .NET development where developers can validate their data and business tiers using these services and generate a T-SQL code out of the same. This might make a small application team self-sufficient to a certain level, but if it's used with the intention of making the database a slave of application, it would be suicidal attack on the project in my viewpoint :)

Vote for feature addition to BIDS and SSMS Suggestion on Connect

After motivation from a Microsoft SQL Server Development Team Member (Maria Balsamo), I have opened a connect request (feedback) for feature addition to BIDS and SSMS which I described in my previous article that can be read from here. This connect item can be read from here, and I suggest that if you feel appropriate, vote this connect item which can potentially result into these feature additions into the future versions or in the present version if the same gets decided to be released in some service packs.

I would also like to hear back from the community, on what you think on these suggestions of mine. Is this some crazy feature greed that has struck me, or I have given words to what most of us wish while working with BIDS and SSMS. To provide a quick summary, the feature that I have requested are as below:

1) Addition of a few VSTS 2010 features to BIDS

2) Search in the development environment of SSRS and SSIS in BIDS

3) SSMS should be added more feature for metadata management

4) Profiler for BI Deliverables

5) Design Pattern Templates for BI Projects Types in BIDS

6) Better Deployment Mechanisms in BIDS

7) Better Debugging Mechanisms for SSRS and SSAS in BIDS

DB Pro features not available in VSTS 2010 RC Professional Edition

Today was a very very embarrassing day for me when I learned a lesson that how attention to detail can sometimes make your life very easy. Visual Studio Team System (VSTS) RC 2010 comes with all the DB Pro tools integrated into it. I was working on one such installation, and SQL Server 2008 was installed on the same machine.

I felt the need to compare two databases and I thought of trying out the Compare Schema feature. Surprisingly I was not able to find it out at all, and then my real issue started. I did all I can to figure out what would be the real cause of the same, and I also felt into a suspicion that, it can be the case that SQL Server 2008 R2 might not be supported by this tool like it was with SQL Server 2008 in the GDR 2 release. Out of my impulsive nature, I also posted a query on the forums.

Finally, I figured out a small point which solved this entire puzzle. The installation was of "Professional" edition !! You might not be able to figure out yet, what it has to do with this issue. My feeling is that VSTS 2010 Professional Edition is almost a paralyzed edition for DB development using VSTS. If you compare the database development features available in Professional vs Premium/Ultimate edition, almost no features of DB Pro are available. Check out the feature comparison between these editions from here.

Microsoft Business Intelligence ( MS BI ) Missing Features even after SQL Server 2008 R2 - My Wish List request to Microsoft

The title of this article might give the first impression, that I am a too greedy person. After all this loads of features raining in SQL Server 2008 R2 version majority of which fall in the bucket of BI, if someone is still publishing a wish list then it's definitely an implicit offense. I still will take this post as a medium to put down my greed of features before the community, and let the community review how justifiable is my greed !!

Before I start with my greedy list, I would like to make one point clear. My intention is to help make the product better to help it's real users (developers) tackle real life problems more efficiently. I by no mean intend to discourage the product (coz even my bread and butter depends upon it), but highlight the shortcoming so that the same can be addressed if the community and Microsoft feels appropriate. I keep on providing workaround for the shortcomings of any feature on MSSQLTips.com to help the community do bridge the gap of our present shortcomings, which can be read from here.

1) Addition of a few VSTS 2010 features to BIDS : When I started working with VSTS 2010 RC, I saw that an almost full fledged SQL Query Analyzer is available and honestly I got super jealous. The reason for the same is that when I work with BIDS, I need to build and manage database objects for which I need to use SSMS. Using Server Explorer still I can get similar kind of environment, but it's an MS Access kind of query designer and not SSMS style, that too with no intellisense. VSTS has got classic interface to create class diagrams, and on contrary to that one can't even export SSIS package design to any other formats. Whether it's a master-child package design in SSIS or a Reports and Subreports (which can also been seen as a master child kind of design), a class designer should be available in BIDS where one can map the components to entities of a design.

2) Search in the development environment of SSRS and SSIS in BIDS : I mentioned this topic in one of my earlier posts also. When one lands on the moon of a messy package or report with lots of components added to it, finding a component becomes as good as finding water on moon. There should be features in BIDS which can help locating components by just providing the name of the component. I honor the argument that other tools which might be providing similar features are expensive, but here we are not discussing project economics. It's about how a product can be made an enterprise class tool, providing all the tools of trade a developer would require in day to day development work.

3) SSMS should be added more feature for metadata management : No doubt SSMS has come a long way from it's previous birth where is was known as Enterprise Manager. But still it lacks features, which surprisingly are found in freeware third party add-ins like SSMS Tools pack and SQL Prompt. VSTS 2010 has got DB Pro, which can be best used from SSMS but we use tools like TableDiff for the same. Custom Reports feature was introduced in SSMS, but there is one bug in it which few people would be knowing. If you create a report using BIDS 2008 in SSRS 2008 format, you won't be able to use it even in the R2 release. If you use the SSRS 2005 version, it would work fine. The reason for the same is that the Custom Reports feature use the Reports viewer which is capable of displaying on SSRS 2005 version reports. Such bugs should be cleared off as the functionality has completely got paralyzed due to such bugs.

4) Profiler for BI Deliverables : Profiler is the best surveillance tool I have ever worked, and I have always wished for such tool for SSIS Development. We get a detailed Progress Tab to see the execution progress, but I don't think it matches any near to that class of Profiler. Also if Profiler itself can be made dockable in BIDS with the option of hooking the worker threads of SSIS, Progress Tab can be made much more organized and usable. Presently it loads tons of periodic information which not expand or collapse option in these slices of the same higher level information.

5) Design Pattern Templates for BI Projects Types in BIDS : Microsoft .NET has got some very widely accepted design patterns like MVC and MVP. I have worked in the capacity of a Team Lead for .NET projects at some point of time in my career, and based on my career experience I can say one thing that design patterns are admired like Beer Festival in Europe, but in MS BI world I find design patterns as less as number of pandas left in this world. To provide what I mean by Design Pattern Template, go through one of the design pattern I suggested here. Some of such design patterns can be standardized in SSIS and SSRS development too, and can be provided in the form of template too.

6) Better Deployment Mechanisms in BIDS : I bet that many professionals would agree that the deployment mechanism offered for SSIS and SSRS packages is completely below professional quality. How to automate SSRS Reports Deployment is one of the most searched topic for SSRS, and the page on my blog where I have explained use of RSScripter receives very high hits due to the same reason. SSIS involves deployment of database objects that support it along with SSIS packages, which is not supported out of the box from BIDS.

7) Better Debugging Mechanisms for SSRS and SSAS in BIDS : I have no grievances for SSIS debugging tools, but for SSRS I feel Microsoft completely forgot to provide debugging tools at all. Reports are viewed over a browser, and in a client server environment that same would obviously work over HTTP. There are no tools to debug if a report starts performing too poorly, and the only means to resort it use some HTTP Proxy tools to profile and dissect the flow of content over the wire to debug performance. There is no mechanism to debug the performance of a complex query language like MDX in SSAS development environment and we resort to tools like MDX Studio and MDX Script Analyzer.

This is just my open opinion, and feel free to leave your comments to provide your opinion or educate me wherever you feel my ignorance.

Visual Studio 2010 ( for Database Professionals ) compared to SSMS

I have been recently using Visual Studio 2010 RC, and the first question that would strike your mind if you have worked on the previous version is, "Are you working on VS 2010 for DB Professionals" ? The answer to this question is that, it seems that Microsoft has galvanized VSTE 2008 and VSTS 2008 for DB Professionals into a single integrated release in VSTS 2010.

This brings an added advantage to both the .NET developers and DB developers community. But I see it more advantageous to the .NET community, as SSMS has not done the same level of feature addition as VSTS team. If you go thru the feature list, you would find that entire toolset of DB Pro (Data Dude) is available now in VSTS 2010, but the same tools are more required for folks who work with SSMS, and the same is not available in SSMS. For example the "Compare Schema" feature of DB Pro/Data Dude is available in VSTS 2010 now but as a DB professional I would like it to be available is SSMS as I need to use it more often from there, than from VSTS. An almost full fledged query editor like the one we have in SSMS, is available right from VSTS. But the diagramming capabilities in SSMS are still not as strong as the ones availalbe in VSTS 2010.

As per my opinion, VSTS team has done a great job of fertilizing their platform by borrowing features from SSMS or SMO, but SSMS team has still got a great scope of improvement when compared to VSTS 2010. You can download Visual Studio 2010 RC from here.

Search in SQL Server Integration Services ( SSIS ) Package - Limitation of BIDS

SSIS is the technology I practice the most in my daily work, and it's one of the areas of microsoft business intelligence that I love working the most. Whenever I find limitations of a technology, I try to do two things for the same: First is devise an alternative way to overcome the same and Second is request the feature justifying the need for the same.

One such limitation in BIDS is search functionality in SSIS. A typical complex package can contain 80-100 transforms quite easily. Now consider that I want to search for components in this package, there is no functionality out of the box that can be used in BIDS to search for transforms or tasks that contain the text ( name property ) that one would want to search.

This is one functionality that I love the most in Microsoft Office Applications like Word and Excel. It's also equally true that Office apps cannot be compared to SSIS, but still search is one functionality that makes life more manageable for a developer, especially when one is dragging the mess received in legacy from another developer. At present if you use search, it would just search the xml of the package and show the xml line, but would not move the focus on the component or property that one is looking out.

I hope that Microsoft adds this functionality to future versions of BIDS or some community add-ins like BIDS Helper brings this functionality the BIDS.

Dashboard Collection / Dashboard Gallery - Design idea for Silverlight based Dashboards

I was going through one of the posts by Marco Russo on SQLBlog.com where a shortcoming ( Excel Paste Picture Link feature ) of Excel Services was explained, but I found a very interesting link to a Dashboard gallery from his post. If you are looking out for a chart gallery or dashboards gallery, I suggest that this is the page you should definitely check out. These dashboards are made out of Excel and a custom component called Microcharts, but this gives an idea of different designs of dashboards which can be quite useful when planning for a performancepoint dashboard design layout.

Moreover these dashboards are created out of Excel, so for Microsoft Excel Business Users this should be no less important than a clip-art gallery, as this provides a spectrum of design insight for designing various kind of Excel Dashboards. Also if your are planning for developing your Analytical Reports by using Excel Services, you should be able to design major parts of your reports similar to the different dashboards that can be found on this gallery.

If such designs are created for Dasboards that are based on Silverlight, they would have the additional advantage of an interactive look and feel. I would wish that such dashboard templates should be made available in performancepoint dashboard designer which should be based on silverlight.

Sharepoint 2010 Training , SQL Server 2008 R2 Training , Windows Azure Training - Download Training Kit

I get many queries from my blog readers regarding any training on Microsoft Business Intelligence and related topics from trainers, institutes or some sort of training agency. To be honest, I believe that after a couple of years of working experience in the industry, one should gain maturity enough to self learn new technologies provided necessary resources are available to gain maturity in the technology of choice.

I was a .NET Team lead at some point in my career, but my interest drove me towards Business Intelligence even before MS BI was born. I stepped down from this position and started working back as a developer in MS BI and today I am quite self sufficient in these technologies, and real satisfaction comes to me from the fact that I have not took any specialized training. Thanks to all the books, free resources and information available on the web. Finally I am on the same page where I was before couple years, but this time as a MS BI Tech Lead.

In this post I want to suggest three of the training kits which are available from Microsoft, which can help any DB / BI aspirants to learn different aspects of these technologies.

1. Windows Azure Platform Training Kit
2. SQL Server 2008 R2 Update for Developers Training Kit
3. Sharepoint 2010 Beta Developer Training Kit