Thursday, November 18, 2010

SSIS , Azure , Sync Framework = Economic solution for synchronization business

I'm reading: SSIS , Azure , Sync Framework = Economic solution for synchronization businessTweet this !
SSIS is independently famous as an ETL tool, but often a tool is a good as the purpose it can cater. Often integration is just a piece of the entire process, that hands over the delivered output to the next runner of the relay race.

In today's competitive and budget economy, one of the biggest challenges that architecture designs as well as project costs face are CAPEX ( Capital Expenditure ) and OPEX ( Operational Expenditure ). If you have underutilized resources and your architecture design is such that it can raise CAPEX and/or OPEX over a period of time, it's definitely going to fail when a RFP (Request for Proposal) is sent to the client. To gain competitive edge, today's architecture design propositions necessarily needs to have dual flavors - one in the traditional way and another which includes a cloud based economy design. Cloud involvement as a platform or as a component in the solution design is the only most promising way to provide a competitive edge to your propositions. In a typical microsoft platform solution, in my views, the primary cloud choice is Amazon EC2 and Azure platform is gaining adoption and popularity gradually.

Sync Framework is one of the robust mechanisms to synchronize data across boundaries spanning networks / systems / machines. This framework can be integrated very well with .NET applications, effectively synchronization of a variety of data can be catered by this framework. A very common example is to synchronize backup of a database across geographically separate systems where each terminal need to synchronize from a common master backup of the database.

Now if we join this separate piece of the puzzle, it can form a solution to a very big business that can be seen in almost every enterprise. Huge multinational corporations often have various companies operating under a single umbrealla, for eg. CitiGroup may have CitiBank, CitiCorp, CitiSecurities etc.. each having their own business, and each company might be implementing different IT systems from legacy to cloud based. A few of these might be occasionally connected system to a central CitiGroup headquaters which might be storing centralized key data summarizing all business and geographies.

1) SSIS can be used to provide the first level of integration for each independent child company. The central controlling office might need to gather data and even the child companies might need to receive different kinds of data from the central office. These child companies might be occasionally connected or dedicatedly connected to the head office.

2) After SSIS has kept the parcel ready, Azure platform i.e. cloud can be used as a distribution network. Cloud is an economic choice, considering the time for which storage might be required for occasionally connected systems. Also Windows Azure Content Distribution Network can be utilized to replicate this data near to the geography of the child offices / businesses.

3) Sync Framework supports cloud as a data source (to the best of my knowledge). Storage and retrieval on the cloud can be controlled by sync framework, to customize the kind of replication and synchronization required for each particular businesses.

4) One can hook a .NET application and keep a monitor on these processes. Also if required, Sharepoint can be used for a collaborative control and monitoring of these processes.

Every enterprise need one or another kind of replication and synchronization of data, and using SSIS, Azure and Sync Framework (optionally), solution providers can exploit the hidden businesses that lies in every enterprise. I would be interested to hear on this viewpoint from Sales folks, especially those who deal in MS BI business.

Monday, November 15, 2010

Download SSIS Denali ( 2011 ) CTP1 video tutorial / tour

I'm reading: Download SSIS Denali ( 2011 ) CTP1 video tutorial / tourTweet this !
I can see SQL Server blogosphere getting painted with new discoveries that professionals make by browsing CTP1. Then they make a lot of efforts to draft a step by step tutorial on how to use the new feature and post in on their blogs. This is quite a lot of effort, especially for a lazy person like me. What am I doing to explore new features and share the same? Well my resource detection radar picks up any useful resource that can cut my efforts and help me explore things in an appealing and effective manner. And this time my radar has dug out a video from TechEd Europe 2011, where SSIS Group program manager Steve Swartz demonstrates the upcoming enhancements in SSIS in Denali / 2011 version, along with detailed theoretical explanation. This 1 hr video can be watched as well as downloaded from here.

Those who do not have time and patience to watch this 1Hr video, here are some bullet points in my version of explanation.

1) From a usability perspective, controls toolbox seems more organized. Also some pampering controls like "Source Assistant" are provided for creating connection managers. Fortunately a perfectly working Undo and Redo are in place.

2) Transforms were very brittle in earlier versions, and change in metadata would very easily break the flow. Also the transform were being left almost uneditable once the input source breaks. This has been taken care of now, and a new mapping dialog has come in place to take care of change metadata from input sources. This is a very big welcome change.

3) Project, Parameters and Environments are new concepts that are introduced. Actually again if I look from application development territory, Project and Parameters seems like global and local scoped variables. Now a package contains variables and parameters. Again Parameters and Environment takes the same level semantics of local and global. Packages are the execution units on which these scopes get applied. Check out the video for a better understanding with demonstration.

4) Improved build and deployment model, tighter integration of SSIS with SSMS as well as tighter control and configuration of SSIS from SQL Server. Earlier sometimes SSIS seemed to me more of an independent application, which uses SQL Server just as a storage box. Now it seems much more integrated than earlier version.

5) Data Quality Services and Lineage Analysis are two of the most exciting enhancements that we are looking forward to be introduced in upcoming CTPs. If these points are getting your nerves excited, watch out the video.

Sunday, November 14, 2010

BISM model from the eyes of MVC / MVP design pattern

I'm reading: BISM model from the eyes of MVC / MVP design patternTweet this !
Having spent quite some time in application development world, design pattern is one thing that I cannot take out easily from my head. I was going through a post by T.K.Anand on Analysis Services Roadmap for SQL Server Denali and Beyond and a particular section gave me a glimpse of what looks like Model View Controller / Model View Presenter design pattern in .NET / application development world. Below is an excerpt from the post. I have highlighted my comments inline with these points. Before reading these points, take a look at the first BISM image shared by Microsoft.


The BI Semantic Model can be viewed as a 3-layer model:
  • The Data Model layer that is exposed to client applications. Even though the BISM model is fundamentally relational, it can expose itself using both relational as well as multidimensional interfaces. OLAP-aware client applications such as Excel can consume the multidimensional interface and send MDX queries to the model. On the other hand, a free-form reporting application such as Crescent can use the relational interface and send DAX queries.

[Siddharth]: This can be seen as the view part of the MVC pattern. If I were an application developer I would call this layer as the presentation interface / facade. Crescent is providing support only to BISM models as of now. Crescent and SQL Azure Reporting are starting off on a conservative basis where they are providing support to BISM Models and SQL Azure databases only, respectively.

  • The Business Logic layer that encapsulates the intelligence in the model. The business logic is created by the model author using DAX (Data Analysis Expressions) or MDX (Multidimensional Expressions). DAX is an expression language based on Excel formulas that was introduced in PowerPivot and built on relational concepts. It does not offer the power and flexibility that MDX does, but it is simpler to use and requires minimal tuning. There will always be sophisticated BI applications that need the power of MDX calculations and we envision that the BI Semantic Model will offer the choice of MDX as well, but this will likely come in a release after Denali.

[Siddharth]: This layer can be seen as the controller part of the MVC pattern. BI Semantic Model would offer the choice of MDX and would be available in future versions, means that it is definitely not a priority right now. In my views not just the sophisticated applications ones, but almost all existing applications need MDX. It would have been nice if it would have been the other way round, where MDX support is provided now with partial DAX now and rest in service packs / future release.

  • The Data Access layer that integrates data from various sources – relational databases, business applications, flat files, OData feeds, etc. There are two options for data access – cached and realtime. The cached mode pulls in data from all the sources and stores in the VertiPaq in-memory column store. VertiPaq is a breakthrough technology that encapsulates state-of-art data compression algorithms along with a sophisticated multi-threaded query processor that is optimized for the latest multi-core chipsets, thereby delivering blazing fast performance with no need for indexing, aggregates or tuning. The realtime mode, on the other hand, is a completely passthrough mode that pushes the query processing and business logic evaluation down to the data source, thereby exploiting the capabilities of the source system and avoiding the need to copy the data into the VertiPaq store. Obviously there is a tradeoff between these two modes (the high performance of VertiPaq versus the latency and overhead associated with copying the data into VertiPaq) and that choice is left to the model author.

[Siddharth]: This layer can be seen as the model part of MVC pattern. If I am getting this right, this means MOLAP evoparates here. No doubt Vertipaq is the demand of time, but legacy applications cannot make an entry into this layer, or source data from their data marts to this layer and adopt this model.

In my views, neither Microsoft nor Developer community can decide what industry has to adopt. Only customer needs and market standpoint of BISM in comparison to it's competition can decide the success of BISM as the probable successor of it's predecessor.

Friday, November 12, 2010

Future of MOLAP, Cube, MDX against BISM

I'm reading: Future of MOLAP, Cube, MDX against BISMTweet this !
Microsoft is of the opinion that MOLAP and BISM would survive together like C++ and C# in .NET. If I think as a customer, when I am aware of the roadmap that has been proposed, I would neither go for BISM nor try to get any more of MOLAP in my territory as the current roadmap would confuse me. BISM would not be completely mature in the Denali version, and MOLAP is under that threat of getting obsolete sooner or later. When someone invests millions into a product / technology, one wants to make sure that the product would continue to live till the next 8-10 years, though with newer blood induced through new releases/versions and medications in the form of service packs. As per the comment by Amir Netz on Chris's post, one statement says "Even with VertiPaq BISM introduced, MOLAP UDM is still used for all the high end heavy lifting" and one another statement says "VertiPaq BISM is advancing rapidly and is able to take on broader and broader workloads", and I translate it as, till VertiPaq BISM does not reach it's target, MOLAP UDM would provide cover-fire. Considering performance of BISM and in-memory vertipaq demonstrated in the PASS summit, I do not think it would be later than SQL Server 2011 R2 kind of version, for BISM to replace MOLAP in terms of maturity. In terms of adoption, it really depends how it propagates into the client territory.

Developer community is quite concerned and showing mixed reactions to this evolution. But one another community that would be concerned with this evolution is the partner products community, like Panorama, WhereScape RED and others. In my views, one of the factors that keeps a technology survived is the ecosystem of partner products that are bound to it. If the concept of Cube, MOLAP, MDX etc are wiped out, even then upto an extent they would be survived by partner products by building a layer over BISM.

a) If customers would feel the need to have the development environment operate in the same way, ISVs would come out with products that act like a shell. DAX can be seen as a shell around MDX, like Windows GUI is a shell around the core OS APIs. For eg. Tools / Utilities such as MDX to DAX converter, MOLAP and BISM integrator, or a Panorama kind of environment which acts like a managing shell over powerpivot can come to rescue if BISM is not getting popular either with developers / customers. Also MS can provide additional features in MOLAP that compensates / simulates what is available in BISM environment, and even if MS won't, ISVs would pick up this business if there is a strong requirement from the customers.

b) And if customers would find the new evolution of BISM tempting enough to upgrade / abandon the old MOLAP , in that case we were always running through a risk of this change and it was just a matter of time when this change would occur. We would be lucky even in this case as Microsoft has catered this required change, instead of a non-microsoft BI platform vendor.

Complex Event Processing, Columnar databases, Cloud computing etc are not new concepts and it's only that Microsoft is expanding it's BI territory now and introducing these players one by one in the form of StreamInsight, Project Apollo, Windows Azure and SQL Azure etc. BISM might be a new mixture of various old and new concepts, but if it outperforms MOLAP performance, it deserves to be the new leader in OLAP space replacing MOLAP.

I am of the strong opinion that MOLAP and probably MDX too would go obsolete sooner or later, but those who fear extinction of their jobs due to this evolution, I would say that there is no reason for the same. Even if Microsoft undergoes a drastic change, partner product community would find a business opportunity in this event, and would exploit the business of rescuing legacy customers, effectively jobs for SSAS developers. It is a similar kind of threat that DBAs might be feeling with the popularity of cloud, but a new generation of computing requires a new generation of DBA. If you feel that SSAS, MOLAP and MDX were too hard for relational brains and your job has been secure due to the scarce availability of developers who practice this skill than the quality of your consulting in providing data warehousing solutions, I feel that BISM can be the kick that might wake you up from this sedative.

Thursday, November 11, 2010

SQL Server Denali - Boost to SSIS career, Start of End to SSAS and MDX career?

I'm reading: SQL Server Denali - Boost to SSIS career, Start of End to SSAS and MDX career?Tweet this !
I am not fortunate enough to financially afford travelling all the way to US to attend PASS summit, but fortunately SQL Server blogsphere is quite vibrant to broadcast regular updates from the summit, so professionals like me can receive regular updates. SSIS is definitely being added more capacities and the enhancements related to data quality is definitely going to increase the applicability of SSIS in a broader range of solutions. On the other hand, this post from Chris which shows the SSAS roadmap shared by Microsoft, makes all the SSAS professionals quite concerned. I would say that, with microsoft, mutation never stops, and the mutation from SSAS Cubes and MDX to BI Semantic Model (BISM) is quite huge as it changes the way we as developers have looked at SSAS right from version 2005. And those who have got an expert level knowledge in SSAS are going to feel more pain at this moment, rather than the novice ones.

One of the investments principles of Warren Buffet says that "Don't put all your eggs in one basket", and this investment theory applies not only to finance, but also to the skills that define our career. The emotion that Chris shares due to the mutation that he can envision in the career of SSAS professionals as a direct effect of BISM, I am able to relate myself with this emotion very well as I have gone through the same sentiments in my career long back. I started my career as a Visual Basic 5/6 programmer. I spent quite a lot of time hopping from one version to another version of VB. Suddenly one day Microsoft .Net was announced, and I felt like "All hell broke loose". I managed to recover and adapted myself to the mutation, instead of a smooth transition, as VB and .Net are two different worlds.

The lesson I took from this experience is never to depend upon one skill set as neither it would help me become a successful architect, nor it would keep my career safe and stable on Microsoft platform. I executed a project as a .NET Project Lead long back and I embraced MS BI with the advent of SQL Server 2005 right from it's CTP. Being in MS BI, I have ensured that I gain experience on different projects which brings me diversified experience on SSIS, SSAS and SSRS with my expertise on SSIS. I have worked as an ETL Lead using SSIS, as a Senior Developer using SSAS and MDX, as a Senior Developer on Performancepoint, and SSRS has been a garnishing ingredient in most of the projects that I have worked. And though being deeply submerged in MS BI, I keep myself updated on .NET, Cloud computing technologies like Windows Azure and SQL Azure, to the level that if I need to board this ship in a catastrophic situation, this option remains open for me. As a by product of this exercise, I gain a deep insight into the end-to-end implementation of a solution involving .net application, SQL Server based OLTP and MS BI components, Sharepoint based dashboards and much more.

I feel that we should take this change constructively, and start aligning our careers in the right direction. And thanks to Microsoft that they have shared this roadmap quite early, so that we have enough time to position ourselves and our clients in the right direction.
Related Posts with Thumbnails