Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«October 2015»

Data Warehouse from the Ground Up at SQL Saturday Orlando, FL on Oct. 10th

SQL Saturday #442SQL Saturday #442 is upon us and yours truly will be presenting in Orlando, Florida on October 10th alongside Mitchell Pearson (b|t). The session is scheduled at 10:35 AM and will last until 11:35 AM. I’m very excited to be presenting at SQL Saturday Orlando this year as it’ll be my first presenting this session in person and my first time speaking at SQL Saturday Orlando! If you haven’t registered yet for this event, you need to do that. This event will be top notch!

My session is called Designing a Data Warehouse from the Ground Up. What if you could approach any business process in your organization and quickly design an effective and optimal dimensional model using a standardized step-by-step method? In this session I’ll discuss the steps required to design a unified dimensional model that is optimized for reporting and follows widely accepted best practices. We’ll also discuss how the design of our dimensional model affects a SQL Server Analysis Services solution and how the choices we make during the data warehouse design phase can make or break our SSAS cubes. You may remember that I did this session a while back for Pragmatic Works via webinar. I’ll be doing the same session at SQL Saturday Orlando but on-prem! ;)

So get signed up for this event now! It’s only 11 days away!

Read more

Create Date Dimension with Fiscal and Time

Here are three scripts that create and Date and Time Dimension and can add the fiscal columns too. First run the Dim Date script first to create the DimDate table. Make sure you change the start date and end date on the script to your preference. Then run the add Fiscal Dates scripts to add the fiscal columns. Make sure you alter the Fiscal script to set the date offset amount. The comments in the script will help you with this.

This zip file contains three SQL scripts.

Create Dim Date

Create Dim Time

Add Fiscal Dates

These will create a Date Dimension table and allow you to run the add fiscal script to add the fiscal columns if you desire. The Create Dim Time will create a time dimension with every second of the day for those that need actual time analysis of your data.

Make sure you set the start date and end date in the create dim date script. Set the dateoffset in the fiscal script.

Download the script here:


Read more

How I Got my Start at Pragmatic Works

October 4th of this month was my seven year anniversary as an employee of Pragmatic Works. Things have changed a lot over the past seven years. Working with the wonderful people at Pragmatic Works has been quite an amazing journey and incredible opportunity. With that in mind I thought that I should share my story of how I ended up working in the business intelligence field with the great team at Pragmatic Works.

The Beginning

In mid 2008 I was working my way through college loading trucks at UPS. I don’t mean the little trucks that deliver to your house but the big, honking 50+ foot trailers. I was an area supervisor and had been with UPS for six years. UPS was a tough job with little thanks or support from management and I was looking for any opportunity in another job or career. It was that bad.

One day my wife, Angela, and I were hanging out with some good friends of ours, Devin and Erin Knight. We were over at the Knight house enjoying some good company when Devin asked if I wanted to do SQL. Devin pulled out his laptop and fired up SQL Server Management Studio and showed me a few select statements. He explained the concept of a database to me and how the data was stored in tables but that we could write queries against the tables to retrieve the data.

The Ryan's hanging out with the Knight'sI was very intrigued because I’ve always enjoyed working with computers. My experience with computers up to this point was limited to designing web pages with Adobe Dreamweaver and building armies in Age of Empires but I always thought I’d enjoy working with computers. I just thought that web design and graphics would be the career I would choose since it allowed me to combine my love of art and computers into one job. Devin was just starting out in his career as a SQL Server developer but I knew he was enjoying it and since I was hating life as a minion of the UPS-machine I was definitely excited about this opportunity.

Devin explained to me how his brother, Brian Knight, was starting this training program where students would learn about SQL Server and SQL Server Reporting Services in hopes that we could then find work as a SQL Server developer. It was one of Brian’s ways of giving back to the community. The training would take place over a week or so.

Inside my head I was thinking, “Man, this sounds awesome! Anything other than UPS is a win in my book but this sounds amazing!” but I made sure to play it cool. “It sounds interesting,” I told Devin, slowly nodding my head.

Devin put me in touch with Brian, who I’d met a few times before as a friend of Devin, and we scheduled the training to begin after I finished my very early shift at UPS. The sunrise shift at UPS ended at about 9:00 AM so I would head home directly after my shift was over, clean up and drive out to Green Cove Springs, FL where the Pragmatic Works office was located. There I would begin learning as much as I could about SQL Server.

Learning Begins

image In those early days of Pragmatic Works the offices were very small, unpleasantly located in a small strip mall next to a hair salon and a dog groomer. The space we used was one small room that couldn’t have been much more than 500 square feet in size with a small closet and even smaller bathroo

Read more

Choosing the Right Microsoft Reporting Technology Part 2: Excel

  • 2 May 2012
  • Author: DevinKnight
  • Number of views: 4763

This post is a continuation of a series started to help you understand each of the Microsoft presentation layer tools purpose and when you should choose each for your reporting solution.  The decision to pick the appropriate tool is difficult but so very important as I detailed in part one of this series.  As I continue the discussion here again are the different tools I will detail:

  • Reporting Services
  • Excel (From the perspective of building PivotTables without PowerPivot)
  • PowerPivot
  • PerformancePoint
  • Power View (Yes Power View is supposed to be considered part of Reporting Services in SQL Server 2012, but it really is an entirely different tool than SSRS is traditionally thought of as)

In part one of the series you learned about Reporting Services and the role it plays mainly as a static reporting tool.  Here in part two we will discuss the polar opposite, which is Excel. 


What it is

As you can see in my list I’ve made sure to differentiate Excel and PowerPivot as two separate tools even though PowerPivot is part of (free add in) Excel.  So to be clear when I say Excel I’m referring to Excel PivotTables off of an Analysis Services data source.  Of course any business user will tell you that PivotTables are possible without an OLAP data source, but to keep this relevant to Business Intelligence I will focus on Analysis Services as a data source.  With that focus in mind let’s discuss the tool.

Excel is customarily thought of as an ad hoc reporting tool that is great for getting a quick answer from your data source.  This is where quick analysis can be done without having to wait days for a report developer to define specs, write a source query and then finally build the report.  The report below for example was literally built in less than a minute. 


Usually when I teach Analysis Services to a group my first question is, “How many of you have dozens of reports waiting for you or someone else to build when you get back to work?”  Predictably about half the room raises their hand and I go on to explain that part of the goal when building an OLAP database is to take much of the reporting burden off of you and move that to your more savvy end users that are comfortable in Excel.  This is possible because after you have built the Analysis Services layer Excel can consume it in a way that give users the ability to drag and drop fields into a PivotTable with very little training required.  This was one of the earliest forms of Self services BI.  So now many of those reports that are waiting in your queue can likely easily be created by the users themselves.  Of course this doesn’t solve all reporting needs for users but it can definitely relieve some stress. 

Because Excel is most end users comfort zone you will find they need little training.  In fact admittedly I have learned a thing or two from end users about reporting in Excel.  Your diving into the end user’s bread and butter and that is why this is such a popular option for reporting.  Be wary though because end users love it so much they may start going beyond Excel’s intended purpose as a reporting platform.

What it isn’t

While Excel is great for cranking out reports quickly many think it lacks some customization features.  Now I happen to think this is partially a myth because we’re IT folks and don’t play in Excel all day.  Like I said earlier I’ve had too many experience where an end user has taught me something I didn’t think was possible in Excel.  Even though I identify it as partial myth I would still argue that tools like Reporting Services are far more customizable with with reports they produce. 

Excel also is not necessarily the right tool for static reports.  As mentioned earlier it’s purpose is for ad hoc reporting, but if you use it for more permanent reports it’s not the end of the world.  You may be just miss out on the benefit of other reporting tools.  For example, let’s say your sales team looks at a weekly report of their previous weeks sales compared to last year.  If this report was in excel they could either open the file each week or view it in SharePoint with Excel Services, but the better solution may be to have it emailed directly to the sales team members using a Reporting Services report and subscriptions.  Having it in Reporting Services also opens the doors for features like report caching if performance becomes a problem.

Who Uses it

No long explanation needed here because this tool is for end users as previously detailed. 

A couple caveats I’ll mention here are:

  • Excel is a far superior tool for testing KPIs than the built in Analysis Services KPI browser.
  • In SQL Server 2012 Excel becomes the primary way that developers test tabular BISM designs.

How is it consumed

The two typical ways of consuming Excel PivotTable reports are opening the .xls or .xlsx file itself or SharePoint with Excel Services.  Ideally your company would go the Excel Services route otherwise an unorganized chaos of workbooks being emailed or placed on shared drives can become a mess. 

Excel Services allows you to place the workbook in a web facing version of Excel.  It does not require that Excel is installed on the users machine because they can view the Excel workbook directly through their web browser.  Any content deployed to Excel Services is managed through SharePoint content databases.


One major limitation some find is depending on the version of Excel you are running that row limits exist.  I would argue that you are not using the Excel PivotTables for their intended purpose if you get anywhere near those limits. 

I have also found that occasionally Excel can write some rather poor MDX against a cube with the drag and drop interface and unfortunately you do not have much control over it.  There is a nice free tool available called OLAP PivotTable Extensions which allow you to right-click on a cell and it provides the MDX that led to the results.  This can be pretty helpful when you’re first learning MDX (the query language for Multidimensional Analysis Services).


As we go through this series remember these high level characteristics about Excel:

  • Most end user preference
  • For ad hoc report development
  • Can be used for static reports but may not be best fit
  • Excel Services with SharePoint best way to store and share reports

I hope you’ve found this helpful and stay tuned for the Part 3 in this series on PowerPivot.  To read any of the other parts to this series follow the links below.

Rate this article:
No rating


Other posts by DevinKnight

Please login or register to post comments.