SSAS Interview Questions / SSAS Training Curriculum

Whenever one wants to learn something or make sure one is competent enough to take the helm of any challenge in a particular technology, the first thing one needs to know is what one should be knowing. In simple words one should be aware of the topics that one needs to cover, then the next point is how much ground has already been covered and how much is yet to be covered. Below is a list of roughly drafted high level areas of SSAS in no particular order, which can be considered as a descent coverage, whether it's considered for SSAS training / SSAS interview. Keep in view that though the below coverage covers a major ground, it's not exhaustive and it can be used as a reference check to make sure you cover enough in your trainings / to make sure you have covered major fundamental areas.

• Types of Dimensions
• Types of Measures
• Types of relationships between dimensions and measuregroups: None (IgnoreUnrelatedDimensions), Fact, Regular, Reference, Many to Many, Data Mining
• Star Vs Snowflake schema and Dimensional modeling
• Data storage modes - MOLAP, ROLAP, HOLAP
• MDX Query syntax
• Functions used commonly in MDX like Filter, Descendants, BAsc and others
• Difference between EXISTS AND EXISTING, NON EMPTY keyword and function, NON_EMPTY_BEHAVIOR, ParallelPeriod, AUTOEXISTS
• Difference between static and dynamic set
• Difference between natural and unnatural hierarchy, attribute relationships
• Difference between rigid and flexible relationships
• Difference between attirubte hierarchy and user hierarchy
• Dimension, Hierarchy, Level, and Members
• Difference between database dimension and cube dimension
• Importance of CALCULATE keyword in MDX script, data pass and limiting cube space
• Effect of materialize
• Partition processing and Aggregation Usage Wizard
• Perspectives, Translations, Linked Object Wizard
• Handling late arriving dimensions / early arriving facts
• Proactive caching, Lazy aggregations
• Partition processing options
• Role playing Dimensions, Junk Dimensions, Conformed Dimensions, SCD and other types of dimensions
• Parent Child Hierarchy, NamingTemplate property, MemberWithLeafLevelData property
• Cube performance, MDX performance
• How to pass parameter in MDX
• SSAS 2005 vs SSAS 2008
• Dimension security vs Cell security
• SCOPE statement, THIS keyword, SUBCUBE
• CASE (CASE, WHEN, THEN, ELSE, END) statement, IF THEN END IF, IS keyword, HAVING clause
• CELL CALCULATION and CONDITION clause
• RECURSION and FREEZE statement
• Common types of errors encountered while processing a dimension / measure groups / cube
• Logging and monitoring MDX scripts and cube performance

How SSIS Performance can be optimized using Change Data Capture and/or Change Tracking , MERGE , and Filtered Indexes

Delta detection, source data extraction and smooth sourcing of input data from OLTP and/or Relational data warehouse is a part of the overall ETL process. Though the performance optimization boundary is outside the regime of SSIS, but these are the fuelling stations of SSIS. OLTP and relational data warehouse are the two main data stations from which SSIS fetches and/or loads data, and relational data warehouse (i.e. data marts / ODS, whatever be the case) is often used to read as well as write data, and optimizing these relational sources can contribute to performance optimization of SSIS packages. In case if you are using SQL Server 2008 platform, some of those that can be effectively used for optimizing data access especially by SSIS packages are as below:

1) Change Data Capture (CDC) and/or Change Tracking: I would not go into the details of the difference between Change Data Capture and Change Tracking. But this is one of the effective means of delta detection. Actually more than performance, it provides a controlled and automated infrastructure for delta detection. Depending upon the kind of algorithm followed in the master ETL package that handles delta detection and extraction, CDC / Change Tracking can be used. A whitepaper that explain in depth methodology of integrating CDC with SSIS can be downloaded from here.

2) MERGE statement: MERGE is one of the smart means to off load processing of data for Slowly Changing Dimensions. A nice brief article from kimballgroup explaining the same concept can be read from here.

3) Filtered Indexes: Mostly everyone is aware of two types of indexes in SQL Server - Clustered and Non-Clustered. But Filtered Index is a new advancement and is a very useful weapon to optimize OLTP as well as relational data warehouse for optimizing data access without impacting the overall system or impacting only the system for which SSIS needs a helping hand from the relational sources for faster data access. Again I would not go into the details of filtered indexes, and a thorough section that explains the same can be read on MSDN from here.

Mostly we talk of how to optimize SSIS package performance, but the bigger picture is how do we optimize ETL process where SSIS is the driving ETL tool, and these pointers can provide some direction in an effort to optimize SSIS package performance, though indirectly.

Download SSIS Denali Video Tutorials and Master Data Services Video Tutorials

SSIS Team has posted lot of videos and resources on the SQL Server Denali Resource Center. My blog readers would have noticed that I have added a permanent link before a week. Recently SSIS Team have shared couple of SSIS Denali Videos on their blog, and without any big background story, keeping it plain and simple, find these videos listed below available for your download. Also there's a How-to video made available on Technet, which is related to Master Data Services and it explains how to import data into Master Data Services in SQL Server 2008 R2.

Master Data Services Video Tutorial

• Import data into Master Data Services

SQL Server Integration Services Denali Video Tutorials

Migration and Deployment

• Working with Integration Services projects in Business Intelligence Development Studio (BIDS)
• Using the Deployment Wizard to deploy an Integration Services project
• Import an Integration Services Project from the Project Deployment File
• Import an Integration Services Project from an Integration Services Catalog
• Migrate an Integration Services 2008 Project to the Project Deployment Model
• Configure an Integration Services project using SQL Server Management Studio (SSMS)
• Running a package using SQL Server Management Studio (SSMS)

Impact Analysis and Data Lineage

• Extract Dependency Information Using the DependencyExtractor Command Prompt Tool
• Extract and View Dependency Information
• Extract and View Dependency Information for SQL Server Objects Using the MSSQL Provider

SSIS Designer Enhancements

• Working with the Data Flow Task's Source Assistant and Destination Assistant
• Resolve Column References in a Data Flow Component

Lineage Analysis in SSIS

In the recently released video / webcast of "What's new in SSIS Denali", SSIS team talks a little about Lineage analysis and the efforts they are making in this direction. The purpose that seems behind these efforts are to provide a transparent tracking of data flow end-to-end right from it's source where the data comes into the scope of tracking till the data makes to the end-user territory i.e. dashboards or excel. This can potentially open the scope of a new business stream in itself, but still that is far.

Lineage analysis is quite important even in the ETL development methodology. In SSIS, lineage of a data stream or I can say lineage of each field is tracked using a LineageID property. Understanding of SSIS architecture is quite important to understand where and why LineageID changes. Wherever the transformation is asynchronous, or precisely speaking whenever the input buffer and output buffers from the transformation are different due to the processing that a transformation applies on the buffer, the source of data generation can be considered as the new lap in the race.

This can been seen like a relay race where one runner hands over the baton to the next runner, and here the baton is the data. But when the race ends, one needs to know that how many runners exchanged the baton and which runner kept the baton for what amount of time. This is logically what we mean by lineage analysis. Whenever an asynchronous transformation is used, it creates a closing door for the logical flow before it, and an opening door for the logical flow after it. Such logical flows are classified as execution trees in SSIS. Support for lineage analysis is quite limited out-of-box in SSIS, and you can use Metadata Toolkit 2008 upto an extent for the same.

Taking this lineage analysis to a higher level, the same kind of analysis across the end-to-end BI lifecycle right from OLTP till Dashboards is very difficult to envision, implement, track and analyze. This would require some service contracts between MS BI services i.e. SSIS - SSAS - SSRS at least. When this data would make it to dashboarding platform which is as of now Performancepoint Services in Sharepoint 2010, it would again have to have a new kind of integration with Sharepoint which is out of SQL Server territory. Still there seems to be a way out as Sharepoint feeds on SQL Server. This is probably what we can expect to see in future.

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

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.

Download SSIS Denali ( 2011 ) CTP1 video tutorial / tour

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.

BISM model from the eyes of MVC / MVP design pattern

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.

Future of MOLAP, Cube, MDX against BISM

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.

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

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.

Project Atlanta and Quest Software's Azure Services : Cloud based services related to SQL Server

Entire blogosphere related to SQL Server is cheering the release of SQL Server Denali CTP1, and everyone seems to be in a race to figure out the new features in this CTP. Some of the road blocks for developers to test this CTP are limited supported operating systems and number of issues during installation. SSIS seems to be loaded with a lot of enhancements with some great sounding support for data quality with new tools and controls.

Coming to the topic of this post, after the availability of Database Manager for SQL Azure, SQL Azure Reporting is one step forward in bringing SaaS model in SQL Server ecosystem. Project Atlanta and Quest Software's Azure Services are two of the cloud based SQL Server related SaaS / PaaS (as I have not analyzed it thoroughly) services.

Microsoft's official description for Project Atlanta is as follows: "Microsoft Codename Atlanta is a cloud-based service that analyzes installations of Microsoft SQL Server 2008 (and later) and provides proactive alerts to help you avoid downtime and adhere to Microsoft's best practices. Detection of potential issues is based on knowledge provided by experienced Microsoft Support engineers who keep it up to date to leverage the most recent experience in supporting SQL Server deployments around the world". Though it sounds very exciting, there is another dimension to this offering, which Brent has described in his post. Do not miss the conversation between Brent and the Program Manager of Project Atlanta. This is one case study where one can take few lessons, that can be helpful in developing proprietary solution offerings on similar lines.

Seems like Quest software already had the same idea in mind. An excerpt about the video where executives from Quest Software shares their views and perspectives about their cloud based IT services on Windows Azure platform in as follows: "Quest is an early adopter of the Windows Azure platform. They’ve built out an extensive services framework as well as a few initial service offerings that sits on top of Windows Azure Framework and Windows Identity Framework. One of the key elements in the design of Quest’s framework is secure communication and authentication between all the service components and layers, whereby encryption is based on certificates stored within the dedicated certificate store provided by Windows Azure". This is one another case study from which lessons can be taken for your future solution offering on Windows Azure / SQL Azure platform.

If you analyze the trend of the wind, you can easily make out the SQL Server product vendors are gearing up to provide cloud based solutions, some of which might be effective and some might be not. From a solution provider point of view, when your client might request a PaaS / SaaS solution on Windows Azure or SQL Azure, case studies like these can provide good insight from an architecture as well as designing of solution offering standpoint. I am wondering what would be Red Gate 's answer to these services, as SQL Toolsbelt might not be sufficient when cloud comes into picture.

Download SQL Server Denali CTP1

Next version of SQL Server after 2008 R2 has started taking it's baby steps in the Microsoft parlance. This version is called SQL Server Denali as of now, and the first CTP is available for download. You can download the same from here.

From a business intelligence perspective, SSIS is expected to be launched with a couple of improvements in SSIS toolset as well as SSIS designer in BIDS. A new reporting client for SQL Server is also expected. To unlock the suspense of what's new in SQL Server Denali, the only way is to download, install and check out what's new in this version.

In my views, my curiosity to check out some of the enhancements in my expectation are as follows:

1) Is there any improvement in SSMS from a spatial data support point of view?

2) Has the Reports Viewer control in SSMS been fixed to support SSRS 2008 and higher formats?

3) Has Silverlight support been added in SSRS?

4) What are the new enhancements in SSIS toolset as well as SSIS BIDS Designer?

5) Has better support now been provided in SSRS reports designer for reports debugging or we still need to use Fiddler?

6) Has any enhancements been provided for SSRS reports deployment or we still need to depend upon RSScripter?

7) Is there any improvement in SQL Server Agent to support better scheduling and triggering of services and instruction sets like SSIS packages, for example FileWatcher support in scheduler?

8) SSAS reference relationship of dimension with measuregroups has the probability to induce corruption in aggregation due to triggering of processing through partition processing. Has this been improved ?

9) BIDS Helper covers lots of gaps that BIDS does not provide. Has this support been incorporated as a part of BIDS?

10) Freeware utilities like SSMS Toolspack, SQL Search Add-in from Redgate, and DB Pro in Visual Studio are some of the distinct utilities that DB professionals require a lot during development. Has SSMS managed to spare us from using any of these utilities?

The above questions are the rapid-fire 10 questions that came out of my mind while drafting this post. There are many more curious questions popping up at different corners of my mind, and I am going to figure it out myself by checking out Denali. In case if you need food for your curiosity, I suggest doing the same.

Non MS BI Data Sources in a BI or MS BI solution

When someone tells me that integration is quite easy, you just pick up a source, a destination perform import-export, and you are done. I generally reply with a smile. Many products declare that they support reading data from almost any industry standard database / data formats. But sometimes one thought must have struck all of us that is there any standard list of such databases or data file formats. Being a MS BI / Microsoft platform professional, I know and have worked with database like SQL Server and data file formats like MS Access, Excel, XML, RSS Feeds etc. I have even worked with data sources like Oracle and SAP, when we have a solution that involves tools and/or technologies from various providers like Microsoft, IBM, Oracle etc.

Keeping a tab on competition is my characteristic as well as need of my profession, and any of us who have been working with solution provider organizations must have come across situations where we need to use MS BI with non microsoft databases or data-sources. Here is a list compiled by me, and though it's not exhaustive, it provides a large slice of the entire ecosystem. Please feel free to add to it to make the list complete. Whether small or big, each item on the below list has it's own market share in it's own platform with it's target user base. This list is quite handy to have, when some product vendor says that they support all industry standard databases or when someone says that integration / migration of data from source A to destination B is a play.

1) SQL Server
2) Oracle
3) IBM DB2
4) MySQL
5) Sybase
6) Informix
7) PostgreSQL
8) IBM UniVerse
9) IBM Unidata
10) Progress
11) FireBird
12) Interbase
13) Paradox
14) Ingres
15) Mimer SQL
16) Pervasive
17) SQLBase
18) Intersystems Cache
19) Teradata
20) DBMaker
21) Valentina
22) Excel
23) Access
24) Visual FoxPro
25) SQLite
26) FileMaker
27) Sharepoint (Document lists and libraries)
28) SAP
29) IBM AS/400

Data storage on Windows Azure and SQL Azure

Windows Azure, SQL Azure, Blob storage, Local storage, Table storage, Queue storage, web role, worker role, Content Delivery Network, Dev Sync etc.. are some of the buzz words which indicates that there are Azure bees around you. If you are not keeping the pace with the advancements that are constantly being made available on this platform, it might feel like a very tough time when you are faced with a situation when you need to develop a POC or an architecture design that works on Azure platform. Fortunately there are a lot of whitepapers and MSDN library that can help you to get started.

But the first thing that clicks my mind when I start the architecture / solution design, is with some basic questions like, how will I capture data and where will I store data. Maintaining session state, implementing security, auditing, logging, error control and reporting, need for a collaboration platform, scalability and performance considerations, migration considerations etc.. are all vertical placeholders in a architecture design than runs across the solution. But when I need to figure out my horizontal figures, my source and destination are the basic blocks. If you have figured out the data storage as Windows Azure either as a destination / staging location / hosting platform, the answer for data storage is not as simple as "enterprise edition of 64-bit SQL Server", whether it's a BI solution where you might use Azure as a temporary / permanent staging location or an Application solution where you might use Azure in place of on-premise SQL Server. With cloud this gets quite more complex, and factors like pricing and storage capacity comes into consideration.

Recently there's a very nice article published on technet, that is worth keeping in your arsenal as a part of intelligence building exercise on Windows Azure platform, to tackle your first encounter with Windows Azure environment. This article provides nice overview on data storage offerings on Windows Azure platform, and the same can be read from here.

SSIS Warning Framework versus SSAS Warning Framework

Most of us would have seen tons of warnings in the execution results / progress tab of the SSIS package designer, when we would have adopted a lazy development methodology. For example, select all the columns from a table and do not use it, and execute the package. You should see a lot of warning in the progress tab. Of course it's a good sign, but one side effect of it is that it overloads the progress tab with tons of warnings which makes the progress tab overpopulated. Many a times it also happens that one might be aware of the design, but there is no easy way to skip from the warnings. Undo in SSIS package, equivalent of SSAS "Deployment Server Edition" property and Warning Infrastructure similar to SSAS are some of the improvements required in SSIS designer environment, in my views. Undo is hear to be available in the next version, but rest of the two are doubtful to be available.

SSAS designer environment provides great control over the warning framework. This was not that great in 2005 version, but with the enhancements available in 2008 version, the control is completely of a professional level. In fact by placing the control of warning framework in the hands of the developer, SSAS team has almost eliminated the need for a best practices analyzer kind of tool for SSAS. If you edit the SSAS database and check out the "Warnings" tab, you can see all the design time best practices applied, understand the same as well as control which design time decisions you want to apply in your solution and want to see warnings in violation of the same. This control keeps a check on the number of warnings that pops-up during design time, and instead of missing vital warnings in the heap of warnings collection, a developer can actually start treating warnings with same importance as one would treat an error message. If this kind of support is available in SSAS, I do feel that it should not be that hard for SSIS development team to provide the same level of support in SSIS development environment.

With these kind of support, this would make the progress report more clean and controlled. I hope someone from Microsoft SSIS development team gets an eye on this post, though it would clearly reflect my greedy demands :) BTW, I wish all my blog readers a very Happy & Prosperous Diwali ( Festival of Lights celebrated in India ).