SSAS Tabular Model...

When Microsoft decided to provide tabular model by incorporating in memory analytics capability there was a lot of debate and still going on in the BI space. The debate was that which technology is Microsoft going to support in the future, will it be multidimensional or tabular. Well based on discussions in sql server community, it seems like both will coexist with each serving a different type of user base. With lot of in memory analytics vendors in the market, Tabular model will sure be grabbing a lot of attention from Microsoft and the user base. With SQL Server Data Tools in SQL Server 2012 one can create Tabular Model Projects. Please use the following link to get started on SSAS tabular Model Projects. This is available at MSDN (Microsoft).

http://msdn.microsoft.com/en-us/library/hh231689.aspx

One of the most important aspect of tabular model projects would be the .bim file which gets created when a new tabular model project is created. It goes without saying that SQL Server 2012 (SSAS) Tabular Model needs to be installed on the Server.
The next couple of years will be interesting for tabular models as more companies begin to embrace in memory technologies, how they scale and what value they offer for the business. One of the leading vendors in the In Memory space is QilkView.

SSIS Vs Store Procedure/T-SQL

One of the interesting debates/discussions which happen while discussing an SSIS implementation/Strategy is whether to use SSIS as a shell and do majority of the work the transformation/loading work done by SQL/Stored Procedures. The second option is to leverage the features in SSIS package and reduce the amount of TSQL/Stored procedures. In order to arrive at a optimum solution there are certain key points that need to discussed. The first would be the requirement from SSIS solution, in this there could be the following sub categories.
1. What would be the frequency of Data load into the target system.
2. What type of transformation is required.
3. What would be the requirement for the availability of data to the users.
4. What type of hardware is available at this point and how does it play into the requirement.

It is very important to understand the capabilities of the tools available while building the SSIS solution. Each piece of the ETL toolset would have a capability, positives/negatives, it is very important to understand them.
SQL/Stored Procedures are very good at performing SET based operations and CRUD operations. SSIS is very good at doing parallel processing, String based transformations, lot of functions for different type of operations based on data types, also it is graphical in nature. It is also very important to perform lot of testing while doing a SSIS implementation to get a good understanding of performance in the given server/hardware environment. With respect to transformations, get a good understanding of the complexity of the transformations required by the ETL solution. It would also be highly recommended to look at the metadata of the data sources and destinations and see if any design patterns can be arrived at. From a developer's view point it is important to understand the level of code maintenance involved.

In case you to get more insigh into SSIS best practices, please look into the discussion in Linkedin:
http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=236893667&gid=59185&commentID=137243006&goback=%2Egmr_59185&trk=NUS_DISC_Q-subject#commentID_137243006
There are very good points listed in the link above by different SQL Experts, it is worth checking it out.

Analytics Reporting and Dashboards...Part 2

In continuation with my earlier blog post, I had the opportunity to get references to some more Analytics reporting and dashboard products. The good news is there is a wide range of tools, also all of them have offerings for the growing Mobile and tablet market. There is a increasing need for delivering BI Solutions on mobile platforms. The 2 Products I have checked out so far has been Strategy Companion (this works on Windows 7 Professional and Enterprise OS, the software does not install on Windows 7 Premium) and bioXL from Pyramid Analytics (has similar set of requirements from Strategy companion). The web site for strategy companion is www.strategycompanion.com. They have products which work for mobile platform as well, they have very good demo videos on the web site and a very helpful support staff. Once you register for a product download they also offer setup support videos which are very useful. They have 3 products Analyzer enterprise, Analyzer Saas and Analyzer mobile. The web site for Analyzer Mobile is http://strategycompanion.com/analyzer-mobile/ and to quote from the web site: Using a “Build Once – Deploy Everywhere” approach, Analyzer Mobile™ enables you to easily provide mobile BI supporting iPads, iPhones, Android smartphones and tablets, BlackBerry smartphones and tablets and Windows Phone 7. No software of any kind needs to be downloaded to anyone’s desktop or mobile device in order to build or deploy your mobile BI reports and dashboards. I found the demos very impressive and would definitely check out more of this product. There other vendors who have product offering in the same space:
They are CALUMO (http://www.calumo.com), their product offerings range from Reporting/Analysis to Dashboards and KPI's. The line up of products are CALUMO for SSAS, CALUMO Skylights and CALUMO Sparks. The company also offers products for Mobile BI Capabilities.
The next vendor I would like to mention is TARGIT,  http://www.targit.com/en, they have products offering wide range of capabilities, http://www.targit.com/en/Experience-TARGIT/Products/TARGIT-BI-Analytics/Features-Overview, they state that they do social analytics as well, these days there a lot of effort being directed towards getting valuable information from Unstrucutred data. The product which supports SQL Server can be downloaded from http://www.targit.com/en/Experience-TARGIT/Try-TARGIT/With-NAV-cubes.
I am have been spending my time trying to get through these products in terms of features, each one of them look impressive in terms of capabilities. In case anyone of you know more about these products please do drop a comment and share your experiences.

Analytics Reporting and Dashboards...

Recently I started a topic on Microsoft Business Intelligence forum in LinkedIn regarding having a common tool for doing SSAS cube reporting, relational reporting,charts and may be Pivot tables. It was a interesting discussion with comments raging from should we a need a all inclusive tool or have separate tools for different types of reporting. The reason i wanted to bring up this topic is because of the recent announcement of different tools from Microsoft starting with Powerpivot, then Powerview , GeoFlow and also more importantly placing Excel has a central BI tool for self service BI analytics. During this discussion I was sent a link to http://www.pyramidanalytics.com/. One of the much talked about topic in past was when Microsoft decided to end Proclarity and use PerformancePoint as a primary dashboard tool. There were lot of folks who were upset with that move and had been long yearning for a tool like Proclarity. Pyramid analytics has tool called bioXL, quoting from the company's website: Pyramid Analytics bioXL allows users to rapidly query both multi-dimensional and tabular OLAP cubes in SSAS and PowerPivot. Users can view trends, quickly isolate and investigate issues, drilling down to details as required or drill across ("dice") through data dimensions in a tool that reflects all the classic requirements of a cube viewer built with today’s technologies and application design concepts. I feel it has lot of features and potential to be used for Analytics reporting. Here is the link for http://www.pyramidanalytics.com/products/bioXL.aspx, they also offer another product such as bioPoint. In summary there are vendors like pyramidanalytics who have come up with a product to fill the void created by Proclarity.

Tableau and Microsoft BI...

One of recent trends in the space of BI has been the use of tablets to display dashboards and enable business managers to get to know important business metrics on the fly. Adding strength to the Mobile BI space has been the fact that the recent increase in the shipments of tablets , at the same time a drop in PC shipments. Presently business is looking to extend BI capabilities for its user base by wanting to get BI solutions deployed to different platforms/devices. Microsoft at this point has not really gotten into the Mobile Space domain yet but there is a way to extend Microsoft BI to Mobile. Here is a link below where in Jen Underwood from Tableau elaborates on how Microsoft BI on Mobile can be achieved. It is called Tableau and Microsoft BI for Mobile BI. I have extracted some key points from the blog:

1) Tableau’s touch-optimized, business intelligence is available across a wide array of mobile device types, browsers, and operating systems including Windows, iPad and Android - native apps and browser based.  In the BYOD world we live in today, having wide device support is critical. Don't limit your mobile BI consumers to one device type.
2) Mobile users can view, edit or even author new visualizations on their favorite mobile tablets to not only view data but also ask on the fly questions in meetings or while on site to get immediate answers.
Read on further at the link below:
http://www.jenunderwood.com/blog.htm#TableauMobileBI

Excel - 3-D Data Visualization

There has been a ever increasing flow of Data visualization tools in the market. The tools become more effective if the data beneath the tools are clean and meaningful. The most recent version of Tableau has options to connect to Big Data sources and help users analyze such data, Big data has been a important space for a lot of vendors, of course there is a hype factor too. Microsoft recently announced a public preview of a tool called GeoFlow. As per Microsoft: “Geoflow” is a 3D visualization tool that helps users map, explore and interact with both geographic and chronological data, enabling discoveries that could be difficult to identify with traditional 2D tables and charts. Moreover, “Geoflow” emphasizes visual storytelling through collaborative features that show changes in data over time with beautiful screenshots and cinematic, guided video tours.

The output of Geoflow looks pretty slick, I would be interested in knowing how much value 3-D Data visualization provides (I guess the video tours would be very effective). Please check the link below to find out what GeoFlow has to offer, currently it is available along with Excel 2013.
http://www.bing.com/blogs/site_blogs/b/search/archive/2013/04/11/geoflow.aspx
I took the picture from the link above...

MultiDimensional Vs Tabular...

With the release of SQL Server 2012, SSAS has two modes one with MultiDimensional and the other with tabular. Depending on the business needs of the organisation one of the modes can be chosen or the two modes can exist on different servers. Well decisions would need to be made on what mode suits the organisation, one of the ways to approach this problem is that one could do a technological evaluation of the two modes and decide which one to choose. A better way to handle this would be is to use a decision matrix which would combine a good mix of business and technology needs. I would list them here, thanks to Devin Knight(From Pragmaticworks). Here are the following points.
1. The Time required to Develop the solution.
2. The type of Data Sources (What are the data sources is it going to be a relation database  or flat files or web or xml).
3. The Ease of Query Language ( When using Multidimensional one needs to know MDX, when one uses tabular one needs to know DAX, this is kind of similar to excel based functions. Depending on the resources, what would be the learning curve, does the budget allow for staffing new resource who are skilled with either of the languages.)
4. Security (What are the security considerations for the solution to be developed, is security a top priority or not).
5. Presentation Options (What type of reports are required and how are they to be presented. What are the delivery modes for the reports for the end users)
6. Model Compatibility (How does either of the modes adhere to the data model available at hand, is there a good star schema database/dimensional model available or is the modelling of data very varied).
7. Scalability ( How scalable the solution is going to be, what is the data growth going be, by how much the source database going to grow, how much value the data is going to provide).

I find these 7 points cover a good range of questions that can be answered, it tries to incorporate a mix of business and technical factors. Hope the readers find these very helpful.