Friday, January 31, 2014

BI - Spotfire,Cognos...

There has been continuous growth of vendors offering BI solutions which cover varying aspects of business intelligence. There has been a lot of emphasis on visualization, offering BI on mobile devices and making reports more interactive. One of the recent products which i got to look at were Spotfire and Cognos. Spotfire is a BI tool offering from TIBCO, Cognos is from IBM. SQL Server BI stack has been facing challenges from different vendors, Microsoft also has been growing its offerings. Coming to the 2 products Spotfire and Cognos, one of the common pitches I heard from these 2 vendors is that both of them offer zero footprint solutions, both of them are web driven tools and the user gets to access different modules available in the products. How they have pitching against the SSRS solution from Microsoft is that, with SSRS one needs to a have a client install of Visual Studio or BIDS in order to perform report development. Cognos for example has the following components with in their product like Analysis Studio (which geared more towards cube building), Query Studio and Report Studio. With respect to Spotfire i was able to get information only on Reporting and Visualization. The capabilities from Spotfire with respect to Dashboard looked impressive. Spotfire focused on how users can take advantage of Advanced analytics without requiring client side installs and providing more power to Business users.



The next feature which was demonstrated was a concept called Location Analytics, this would allow users to build reports based on maps and would provide details with respect to Cities, roads and political boundaries. One of the features highlighted was the concept of Automatic Offline Geocoding, which seems like a neat concept, need to dwelve on these features more to get a understanding of the benefits.


The next feature demonstrated was the concept of Mobile Metrics and how Spotfire can support Mobile BI Reports. In order to get more details on the offerings from Spotfire, please use the following Link:
http://spotfire.tibco.com/
For details on offerings from Cognos, please visit:
http://www-01.ibm.com/software/analytics/cognos/
It would be interesting to see how the BI space pans in the coming year with emphasis on Cloud based offerings and other social network related features being added into the mix of products.

Thursday, January 23, 2014

SSMS Productivity Tips...

SQL Server Management Studio is the widely used tool for developers working on SQL Server development projects. There are numerous times when developers have to scroll through long pieces of code, figure out the logic, identify what variables are being used. While debugging or even while development scrolling through long pieces of code can become tedious. For example let us say I am working on a long stored procedure, i see a block of code way down where I would like to use a table variable or a temp table instead of the logic being used currently. I need to first scroll back way to the top define the temp table or table variable, then come back to the code section where the logic needs to be modified. This process could become tedious or some times we become lazy to keep scrolling up and down. There is a feature in Sql Server Management Studio where in one can split current query window. In SSMS, open the sql/script file , then in the Window Menu , Choose the Split Option. This would split the current query window into 2 Parts.

Each window can be scrolled independently. In the example I was showing let us say I was working on the Lines 1200-1220, in the bottom part I could focus on the area of the logic, In the top window I can scroll all the way to the top of the code and declare the variables and temp tables. Please see the image below.



There is also a blog post by SQL Server Expert Kendra Little on this very same topic. Here is the link for the blog post: http://www.brentozar.com/archive/2014/01/ssms-trick-edit-large-procedures-with-the-splitter-bar/.
Hope you folks find this tip useful...

Friday, January 17, 2014

UNPIVOT a Table...

One of the common routines that is done while analyzing or reporting information is Pivoting and Unpivoting of data. There are lot of techniques which are employed to achieve the Pivot/Unpivot Transformation of data. I found one example where the CROSS APPLY VALUES operator is used to achieve the unpivot functionality. The implementation is pretty neat and construct is pretty easy to follow, the standard PIVOT, UNPIVOT operators provided by T-SQL sometimes are hard to follow,
Here is the high level construct: the code below is an example which has been taken from:
http://sqlstudies.com/2013/04/01/unpivot-a-table-using-cross-apply/. Please use the link to get the complete code set.

SELECT UnPivotMe.FirstName, UnPivotMe.LastName,
CrossApplied.Question, CrossApplied.Answer
FROM UnPivotMe
CROSS APPLY (VALUES (Question1, Answer1),
(Question2, Answer2),
(Question3, Answer3),
(Question4, Answer4),
(Question5, Answer5))
CrossApplied (Question, Answer)
 
One of the observation that can be made is that there is 5 columns for Question and Answer in the UnPivotMe table, which are finally transformed into rows in the final result.
 
FirstName LastName Question                     Answer
Kenneth    Fisher  What is your first name?     Kenneth
Kenneth    Fisher  What is your favorite color? green
Kenneth    Fisher  What do you do for a living? Not much
Kenneth    Fisher  What is 2x3                  6
Kenneth    Fisher  Why?                         Because
Bob        Smith   What is your first name?     Robert
Bob        Smith   What is your favorite color? blue
Bob        Smith   What is 4x7?                 28
Bob        Smith   What is 7x6                  Life the Universe and Everything
Bob        Smith   Why?                         Why not

Monday, January 6, 2014

ColumnStore Indexes...

With the increasing amounts of data, there is lot of expectations from data warehousing solutions in terms of performance and accuracy. There is a constant demand for increased performance of queries from a data warehouse. With huge amount of rows sometimes performance of queries get affected due to the time spent in pre-aggregating or aggregating data. With this backdrop there has been the emergence of columnstore indexes. What is a columstore index in SQL Server? Quoting from Microsoft Technet: Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries. Typical data warehousing workloads involve summarizing large amounts of data. The techniques typically used in data warehousing and decision support systems to improve performance are pre-computed summary tables, indexed views, OLAP cubes, and so on. Based on this definition what is the advantage over traditional row based storage. In Databases with ColumnStore indexes SQL Server stores data in a columnar format based on the column with columnstore Index. Since data is stored one column At a time, this allow sql server to perform aggregations on the single column very quickly instead of traversing through each row. Also Since only the columns needed must be read this greatly reduces the Disk I/O activity. Most queries do not encompass all columns in a table which means that lesser data would be bought into memory thereby enhancing the speed of the queries.
Some important points to keep in mind with respect to ColumnStore index
Clustered index key – If a base table is a clustered index, all columns in the clustering key must be present in the nonclustered columnstore index. If a column in the clustering key is not listed in the create index statement, it will be added to the columnstore index automatically.
Partitioning – Columnstore indexes works with table partitioning. No change to the table partitioning syntax is required. A columnstore index on a partitioned table must be partition-aligned with the base table. Therefore a nonclustered columnstore index can only be created on a partitioned table if the partitioning column is one of the columns in the columnstore index.

In SQL Server 2012 Columnstore indexes are not updatetable, i guess in SQL Server 2014 there are plans to make them updatetable.