Building A Complete Data Solution Using The Microsoft BI Stack

Microsoft has a full-featured platform for handling the data needs of most companies. It starts with a strong relational database backend for transactional processing and data warehousing (SQL Server), followed by an analytical processing and data mining tool (SSAS), a solid integration service to handle various data migration tasks (SSIS) and finally a good product for various reporting needs (SSRS). On top of all that we have a variety of Excel add-ons for business intelligence and data mining and lately the very well received Power BI platform for self-service data visualization and business intelligence.

This is all great. The problem is that often many of these products end up being used by themselves instead of combining their various strengths to truly build a data management solution that will enable the business to use its own data for maximum benefits.

There are all sorts of resources online that will attempt to make one an expert in any of the products mentioned above … but not that much guidance in terms of really explaining how they’re supposed to work together.

Many technologists start with SQL Server on the OLTP side – we process daily transactions for the business and we make sure that create / update / delete operations are as fast as possible or else customers will go to a competitor.

So now we have all this data that’s accumulating in a relational database and somebody wants to run reports against it. No problem – we know how to do that so we slap together some reports in SSRS – problem solved … except that as time goes on (and we get more and more data) those reports seem to get slower and slower. And no … please don’t run that report that gives us a summary for the whole business for the last 3 years … you know which one … the one that joins 30 tables, takes 10 minutes to run and slows down the entire database.

What’s the problem here? To use the famous quote – “If you only have a hammer, you tend to see every problem as a nail.” We know SQL Server very well but the relational database model for transaction processing is not the ideal backend for business intelligence and analysis.

Somebody then mentions the term “data warehouse” .. and OLAP … and slowly changing dimensions. Oh no – more technology to learn. How exactly do we go from point A (our transactional database) to point B (some sort of data store that business analysts can query with their fancy graphical tools to build all the nice charts they’re looking for).

We need to spend more time looking at how we can integrate all these technologies (most of which we get for free once we purchase SQL Server) in order to satisfy the various data needs for the business.

I recently came across a very good series of hands-on articles (source code included) that explores all these products and creates a nice picture of how they all fit together.

Part 1 – Introduction and OLTP Database Analysis

Part 2 – OLAP Database Objects Modeling

Part 3 – Data Mart Load Approach and Coding

Part 4 – Data Mart Load Using SSIS

Part 5 – Defining A SSAS Project And Its Dimensions

Part 6 – Refining SSAS Data Warehouse Dimensions

Part 7 – Cube Creation, Cube Deployment and Data Validation

Part 8 – Creating A Sample SSRS Report

I hope you find all these useful.

Installing SQL Server 2016 Community Technology Preview 2 – step by step screenshots

Microsoft recently announced the first public Community Technology Preview (CTP2) for SQL Server 2016. It is available for download from this location:

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

If you didn’t get a chance to try it out yet, below are step by step screenshots from a standard SQL Server 2016 CTP2 installation with all options selected. We know that many new features are coming in SQL Server 2016 but from what I saw so far the installation process is pretty much similar to what we’ve seen before in SQL Server 2014.

Some quick observations:

  • SQL Server 2016 can’t be installed on anything below Windows Server 2012 (server-side) or Windows 8 (desktop-side). This is to be expected but still … good to know. I know many .NET developers still run Windows 7 for example as their development environment so SQL Server 2016 won’t install there (whereas SQL Server 2014 installs just fine on Windows 7). Here are the full technical requirements for SQL Server 2016 – https://msdn.microsoft.com/en-us/library/ms143506%28v=sql.130%29.aspx
  • I installed SQL Server 2016 CTP2 on a newly created virtual machine running Windows Server 2012 R2 and I was quite surprised to see as part of the requirements for SQL Server 2016 that it needed Oracle Java JRE 7 Update 51 or higher. I’m not sure yet what component of SQL Server 2016 depends on that – this one will be interesting to watch as more people start trying out the software. I ended up getting the latest Oracle JRE from this location – http://www.oracle.com/technetwork/java/javase/downloads/index.html.

Enjoy!

Ways to keep track of updates for Microsoft SQL Server

Keeping track of updates for Microsoft SQL Server can be a time consuming task. A business/enterprise using Microsoft SQL Server will typically use more than one version of the product and each of them has its own somewhat random schedule of service packs, cumulative updates and hotfixes. To add to all this we have all those ‘fun’ moments when Microsoft will release some update that will cause serious problems with existing software so now we have to know about that aspect of it as well.

So what is a SQL Server dba supposed to do? Where do we go to find all this information in reasonable time?

For quite a while now the resource that was best known for this type of data was the site below:

http://sqlserverbuilds.blogspot.com/

It’s a great site but it has some issues. For example, we don’t know who’s behind it and for how long they might keep maintaining it. There is also no good way to be alerted of changes so now we’d need to use third-party page monitoring services for that (such as http://www.changedetection.com/ or http://watchthatpage.com/).

Other similar pages exist elsewhere on the web – here are two maintained by the SQL Sentry team for the latest versions of SQL Server:

http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2012/ – for SQL Server 2012
http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2014/ – for SQL Server 2014

Even Microsoft apparently has such a page but it’s somewhat hidden in its vast network of tech sites:

https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx – Update Center for Microsoft SQL Server

In May 2015 a newcomer site joined the party – this one is updated and maintained by the awesome folks at Brent Ozar Unlimited:

http://sqlserverupdates.com/

So far this last one seems to be the most useful one – it adds information about support end dates and it also allows one to monitor it for changes via email or RSS – very nice.

There you have it – a few ways to keep on top of Microsoft SQL Server updates and remove some of the pain from that process.

A better way to handle role permissions in ASP.NET Identity – IsInRole vs. HasPermission

ASP.NET Identity is currently the main framework used to add authentication and authorization capabilities to an ASP.NET site. After ASP.NET Identity is integrated with an ASP.NET project it creates a few database tables where relevant user data can be stored. Let’s look at some of these tables:

AspNetUsers – the table where application users are stored
AspNetRoles – this is where we store application roles (you can also think of them as groups)
AspNetUserRoles – a mapping table where we store information about what users belong to what roles

So far, so good. What we get out of the box is a way to create users, create some roles and assign users to roles. This follows the best-practice where we want to eventually assign application permissions to roles instead of individual users.

But how exactly do we handle application permissions given the functionality that ASP.NET Identity provides for us out of the box? Most developers and projects at this point follow the pattern that we’re so used to from other areas in IT – we check if a user belongs to a certain role and if so, we allow that particular operation to proceed. In ASP.NET this is done using the Authorize attribute (allow the operation to proceed if the current user belongs to a certain role):

What is wrong with this approach? Let’s consider a few scenarios:

Read more