Monday, April 4, 2016

SQL Server 2016 - Query Store

Performance tuning is a constant topic of discussion, also one of the top priority items when it comes to good code development, better performing queries provide quicker response times, users/customers are satisfied. There have been constant improvements in the area of performance tuning features in SQL Server, also in the later releases of SQL Server, the execution plans were greatly improved. It is very important for developers to understand execution plans so that they improve queries that cause bottlenecks in the application. One of challenges have been to effectively store execution plans and queries so that they could be reviewed for bottlenecks and improve them. In SQL Server 2016 the concept of query store has been introduced. This Query store feature helps one to gain insight into the query plans and statistics.
In order to enable query store feature in SQL Server 2016, one can use Transact-SQL:

ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON;

The query store feature in SQL Server 2016 can also be enabled through SQL Server Management Studio, by choosing the database properties/query store page.
Quoting from Microsoft MSDN Page: I am listing a couple of scenarios for using query store feature:
  • Quickly find and fix a plan performance regression by forcing the previous query plan. Fix queries that have recently regressed in performance due to execution plan changes.
  • Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource problems.
The MSDN link for Query Store is listed below: The page below provides a more completion explanation of query store.

Friday, April 1, 2016

Data Gravity...

With the advent of Big data, cloud technologies , data science there has been lot of interests in moving towards cloud based solutions for Business Intelligence and other applications. More companies/business are looking towards cloud deployments or showing interest in cloud based solutions. While all this has been going on, there also has been a tremendous growth in volume of data and information being shared and requested. The data volume has also bought into perspective the notion of whether data needs to be present on-premise or should data be moved to the cloud. While researching the ideas being suggested around data volumes, i came across the concept of data gravity. Jen Underwood , Data Analytics and BI expert, has written an excellent article covering the concept of data gravity. Please see the link below for the full article.
http://www.jenunderwood.com/2016/02/27/hybridbi/
She covers the concept of data gravity, the type of solutions available and also some of the vendors who offer solutions. The concept of on-premise vs cloud based BI/Data storage provides interesting insights and would be very useful for folks who are planning their data storage and provisioning strategy. The concept of stretch databases are also covered in the article above. An interesting read on data gravity and types of solutions being offered.

Wednesday, March 30, 2016

Data Modeling...

In today's world of ever growing data and information, one of the areas that has been kind of battling the existence has been data modeling, there are wide ranging opinions about the validity of data modeling both positive and negative. One of the viewpoints favoring data modeling has been to provide a context around the data that needs to be accessed and used, how it can be stored and resented to users. There are number of situations where how the data being organized has to be presented to the business in a concise fashion. There are different types of models within data modeling like conceptual models, logical models and physical models. What do these type of mean and how they differ from each other is very concisely presented in the article below. Quoting from the article: "My uses of conceptual, logical, and physical come from the Information Engineering (IE) methods of data modeling". The article has been written Karen Lopez: Senior Project Manager and Principal Consultant, InfoAdvisors, Inc. 
http://www.datamodel.com/index.php/articles/what-are-conceptual-logical-and-physical-data-models/

I hope the above article provides good explanation especially for folks who are getting into the area of data modeling.

Tuesday, March 22, 2016

Data Science Resource/Blog...

One of the rapidly advancing areas today is Data Analytics/Data Science in the Business Intelligence space. There are lot of resources/tools that are coming explaining the capabilities of Data Science. Data Science has seen tremendous growth alongside the adoption of Big Data technologies. It is a very challenging space to keep up with, one of the blogs that i regularly visit to understand the concepts in Data Science and keep up with the trends in Data Science/Analytics, is http://www.analyticsvidhya.com/. The blog/website is very nicely laid out with content rich articles and tutorials. They cover a variety of tools and also encourage folks interested in data science to participate in challenges/contests. Please do visit the site, it provides amazing content and coverage.

Saturday, March 19, 2016

Experiments - Azure ML Studio

Azure ML studio is very intuitive and a powerful tool that can be used for performing different type of data analysis experiments. The Studio can be used to set up very basic experiments like descriptive statistics to performing complicated machine learning algorithms. The whole layout of the ML studio is very user friendly and in some ways remind me of the layouts in SSIS packages. I decided to setup a basic experiment in ML studio to perform certain descriptive statistics calculations on a sample data set. The dataset I used was adult census data from UCI repository and ran the data set through a R script component to data, once the data was massaged it was run through a descriptive statistics to perform descriptive statistics on each column. Once all the components are in place the experiment can be run in the ML studio to get the results. The data can be verified and if everything looks good, the experiment can be published as a web service and can be accessed by a say .NET program.
I have illustrated the experiment which i have set up in the ML Studio.



Hope this provides a basic overview of ML Studio. Azure ML studio opens up a lot of possibilities to perform different types of data science experiments and bring them more into the main stream.

Sunday, February 14, 2016

Data Lineage/Data Modeling...

With the advent of visualization and sophisticated reporting tools there is one aspect which is very important for analysis of data. For the reports/visualization to be valid and effective, it is very important to have the data behind the charts /graphs to be properly modeled and validated. When analyst look at the reports, they very soon look at what comprises the data element, where the data is sourced from. The analysts would also be interested in how the data has been transformed by the time it is shown in the reports. In order to have the reports/visualization to be effective it is very important to have a strategy for data lineage/data modeling. Typically data is sourced from various sources and finally they end up in data warehouse/data mart. During this process it very important to track how the data moves from one system to another. It is possible that data elements would move through the process with out any transformation. The other possibilities are that the data element could be transformed like there could be casting/trimming, extraction of a data element into multiple columns in the target data mart. The data elements could become part of a calculation or they could end up being candidates for  lookup tables. In order to capture all of the above data movements/transformations, it would be good to have a repository where the Metadata/data lineage can be stored. Such a metadata tool would be very handy for analysts/developers to really understand what makes up the data elements. It can also provide a valid context so that a effective visualization solution can be developed. The other aspect that help provide improved visualizations is also data modeling. Data modeling when done correctly with a proper framework can provide immense benefits to the overall data services visualization strategy. It is extremely important to know the data that is being worked on/extracted. There are products in the market that cover the data lineage piece of the puzzle. Pyramid Analytics, one of the more popular vendors that provide really good analytic tools has come with a product called BI office that has a Governed Data Discovery Platform. Please see the link below for all the details:
http://www.pyramidanalytics.com/pages/bi-office/governed-data-discovery.aspx.
As per the website here is a overview of the Data governance feature in BI Office:
"BI Office provides you with self-service content creation in a centralized, shared paradigm – that also tracks the content life-cycle".
When companies/Business build a data services/data provisioning strategy, data governance needs to be an integral part of the whole vision. As the usage begins to scale and there is more demand for services, data governance becomes more and more critical to make the data services strategy successful.

Friday, January 15, 2016

SSRS 2016-New Features...

With a plethora of reporting, data visualization tools ,SSRS was getting buried in the mix. The question was would SSRS 2016 get any major updates or would that kind of go away slowly with other tools taking prominence. The good news is that SSRS 2016 is getting lot of new features, here are some of them: Reporting Services Web Portal: The usual SSRS portal has undergone a major revamp and the portal looks more in tune with the current dash board offerings from other vendors. One of the major change has been the use of HTML 5 in SSRS 2016 which should allow reports to be portable across devices and increase the offering s more effectively. There is also a feature being added called Mobile report Publisher where in you can publish SQL Server mobile reports to your reporting portal. For more detailed explanation of SSRS 2016 features, please refer to the link here below:
https://msdn.microsoft.com/en-us/library/ms170438.aspx