Boyan Penev on Microsoft BI

If we want to calculate the number of weekdays between two dates in SQL Server we do not have an in-built function doing that and we have to create our own. There are a number of approaches to doing that and I will describe a few.

1. Using a Date table

If we have a table containing dates, we could add a column to it, which shows if a date is a Weekday (1) or a Weekend (0). Then we could simply sum the values in our query. This is an easy approach, but would not be the most optimal one, as you will see later.

2. Building a function

We can build a function which takes the start and end dates as parameters and then returns the number of weekdays between the two. One way of building that would be to cycle through all the dates between the start and the end, and determine if each of those is a weekday, incrementing a variable if it is. However, this would require looping many times for possibly many dates, which can be slow.

3. SQL Expression

And lastly, if we want a fast performing solution, we can write an expression which will do the work in one go without looping through dates and without accessing the database tables. There are a few solutions online, but I could not find a robust one, so I wrote one myself. It seems to be doing fine, so there it goes:

SET DATEFIRST 1 --start of the week on Monday

DECLARE @startDate datetime,
        @endDate datetime

SET @startDate = '2010-08-02' --testing start date
SET @endDate = '2010-08-08' -- testing end date

SELECT CASE
   WHEN @startDate > @endDate THEN 0
   ELSE ((DATEDIFF(dd, @startDate, @endDate)+1) - DATEDIFF(WW,
           DATEADD(dd, -@@DATEFIRST, @startDate),
           DATEADD(dd, -@@DATEFIRST, @endDate))*2
        )-
     CASE
      WHEN DATEPART(weekday, @endDate) = 6 THEN 1
      WHEN DATEPART(weekday, @endDate) = 7 THEN 2
      ELSE 0
     END
     +
     CASE
      WHEN DATEPART(weekday, @startDate) = 7 THEN 1
      ELSE 0
     END
  END

The algorithm does the following:

1. If the start date is after or equal to the end date, return 0
2. Otherwise, get the difference in days between the start and end dates, including both (the +1 bit)
3. Calculate the weeks difference between start and end dates and multiply this by 2, as this is the number of weekend days
4. Subtract the result from 3 from the result from 2
5. If the end date is Saturday, take away one day -> compensates for one extra weekend day
6. If the end date is Sunday, take away two days -> compensates for two extra weekend days
7. If the start date is Sunday, add one day -> add one day, because the week difference is too large

A few interesting findings:

1. DATEDIFF(ww, @startDate, @endDate) does not yield correct results, because it does not take into account the DATEFIRST parameter. Because of that we are adjusting the start and end dates by moving them back.
2. DATEFIRST needs to be set properly (in our case to 1 for start of the week on Monday), unless we are always relying on the default value

Please let me know if you find any issues with the above function.

T-SQL dates, T-SQL, weekdays

Recently I had the (dis)pleasure of working with Business Analysts, who also thought that they are good in dimensional modelling. so, I had to implement BI solutions (including cubes) on top of their database design. I will show an example (about 95% the same as the actual design), where the idea of letting BAs go into dev territory does not yield the best results:

This “dimensional model” was created by an experienced BA. Some “features” are missing here:
1. The fact table had EffectiveFrom and EffectiveTo dates
2. The relationships between some Dim Tables were 1-1 ?!
3. The Time dim (the only one properly implemented on its own – on the bottom of my example) had columns like: DateTimeName nvarchar(100), DateTimeKey nvarchar(100), YearName nvarchar(100), etc..
4. The Some Tables on the top had nothing to do with the rest (in fact a colleague of mine reckons they are there to fill in the white space on the top of the A3 printout)

Another design, which is better, but still pretty bad showed up after my training on Dimensional Modelling (1hr to go through EVERYTHING, including M2M relationships, Parent-Child hierarchies, Type 2 dimensions, etc):

Obviously, the designer (a developer actually) did grasp some concepts. However, my explanation of a star schema must have been not too clear..

Hope that you had some fun with these two diagrams..and I am sure many developers get in a similar situation, especially when someone else designs their databases. But two points:

1. Ask the BAs to analyse the business and their requirements – not to design the database
2. 1 hour of training on dimensional modelling will not make you an expert

SSAS, T-SQL dimensional modelling, dimensions

A colleague of mine asked recently: Do we need to have foreign keys between our fact tables and dim tables in order to take advantage of the new SQL Server 2008 star-join optimisation. I decided to ask the question at the MSDN Forums. Just now I got a reply and I thought it may be good to share with everyone:

Charles Wang – MSFT

To use star join, indexes are required on tables. FK constraints are not necessary, but it is recommended that you have FK constraints defined since without it SQL Server must depend on heuristics to detect star schema query patterns. There are some restrictions for heuristics to determine a start join query. And it may not pick up a correct start join query plan under some cases. You can find the detailed information in the section “Star Join Heuristics” of this article, Data Warehouse Query Performance. The following is an extraction of the content:

Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns. The following heuristics are applied to detect star join query patterns

1. The largest of the tables participating in the n-ary join is considered the fact table. There are additional restrictions on the minimum size of the fact table. For instance, if even the largest table is not beyond a specific size, the n-ary join is not considered a star join.

2.All join conditions of the binary joins in a star join query have to be single column equality predicates. The joins have to be inner joins. While this might sound restrictive, it covers the vast majority of joins between the fact table and dimension tables on the surrogate key in typical star schemas. If a join has a more complex join condition that doesn’t fit the pattern described above, the join is excluded from the star join. A five-way join, for example, can lead to a three-way star join (with two additional joins later on), if two of the joins have more complex join predicates.

There we go – so we need indexes, while FK constraints would be good to have but not necessary. I also wrote to the CSS SQL Server Team, so if they come back with more information, I will update this post.

T-SQL SQL Server 2008, star join

As an addition to Teo Lachev’s Top 10 Wishlists (SSAS and SSRS), I would like to contribute only 1 item to a possible SQL Server DBMS wishlist:

1. Source Control.

Not SourceSafe source control, but rather an automated version out-of-the-box, not relying on developers to check in/out. Rather, it should track the changes to the code as they are made, and a full version history should be available directly in the DBMS. It should not be too hard. After all, there is a nice database available, which can store code with its version numbers just like anything else.

This would make a lot of developers’ lives a bit less frustrating.

OK, a SQL code “beautifier” would also be nice, but it is not all that important…

Vote on Connect

T-SQL source control, T-SQL, version control

There are some noteworthy changes in the way SQL Server 2008 handles security; apart from the single major improvement – the replacement of the Surface Area Configuration tool by Policy-Based Management.

One thing that surprised me today was that even though I was a Domain Admin and a member of the local Administrators group, SQL Server 2008 refused to let me log in. A login had to be explicitly created so I could access the instance. After some research, I found out that in SQL Server 2008 the local Windows administrators do not get mapped to the sysadmin role. Therefore, it is possible to get locked out of a server instance if there are no sysadmins on it. This is a feature, which separates more clearly SQL Server admins and Windows admins.

A further note on this topic. I would have not lost a small SQL Server war on a Dev environment recently if we were using SQL Server 2008 instead of SQL Server 2005. Now, being a Domain Admin does not necessarily win the battle for SQL Server permissions.

There is a TechNet page describing SQL Server 2008 Security Changes for further reference.

And another one, helping in case all system administrators are locked out.

T-SQL security, SQL Server 2008