I don't know datawarehouse modeling

I am finally getting serious about trying to understand BI, Analysis services, etc so I've tried to transform our existing data into something that can be easily consumed by SSAS. In general, reading a technical book before I have a vague foundation for the material is a strategy for failure so I've been trying to cobble together a enough of a star schema with "real" data for my books to make sense

Problem

Our sales areas are defined by Regions. Regions have Divisions and Divisions may have Districts. If they don't have districts, in the data at least, they will still have a fake district, 0. Analysis of the data is typically done by areas which says Dimension. Some sample data is below

region  division  district  rdd
02      03        00        0203-00
02      05        00        0205-00
02      05        01        0205-01
02      05        11        0205-11

While I'm sure it's a "it depends" question, would I want to model this as multiple tables or can I make it work with a single dimension? If I make it multiple tables, does this look like I'm going down the right path?

Any comments, pointers, help, etc are greatly appreciated.

Datediff, not doing what you think it does

I think two different defects logged in 7 days qualifies for a blog post.

datediff is not not the inverse of dateadd

Are you using datediff and have you noticed it's rounding on you? It's not rounding, it's doing exactly what it's supposed to. Really, read BOL and you'll see the issue is that datediff doesn't do what you might think it does. Of note, "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate."

As a quick example, see below. Start date is June 2000 and it assumes it is January 1 2010. That is 9.5 years later. Adding 10 years (dateadd) to our start date will show the correct date of 2010-06-01. Datediff also correctly identifies that 10 year boundaries have been crossed between 2000 and 2010

DECLARE
    @start_date datetime
,   @current_date datetime

SELECT
    @start_date = '2000-06-01'
,   @current_date = '2010-01-01'

SELECT 
    datediff(year, @start_date, @current_date) AS nine_and_half_years
,   dateadd(year, 10, @start_date) AS ten_years

nine_and_half_years     ten_years
10                      2010-06-01 00:00:00.000

So, in conclusion

datediff is not not the inverse of dateadd

References http://msdn.microsoft.com/en-us/library/ms187081(SQL.90).aspx Comparing DATEADD and DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794.aspx datediff
http://msdn.microsoft.com/en-us/library/ms186819.aspx dateadd

User Defined Table Type permissions

File this one under "these aren't the permissions you're looking for"  

As noted in http://billfellows.blogspot.com/2010/06/t-sql-tuesday-007-summertime-in-sql.html we have begun using TVPs at my work and they've been working out well for us.  One thing I noticed was that I didn't have permission to actually use them in environments where I was not an admin.  When I attempted to use a user defined table type, SQL Server would bark with "The EXECUTE permission was denied on the object 'FOO', database 'BAR', schema 'dbo'."

Executing the following statement

DECLARE @t1 dbo.CONTRIVED_EXAMPLE

results in 

Msg 229, Level 14, State 5, Line 1
The EXECUTE permission was denied on the object 'CONTRIVED_EXAMPLE', database 'TSQL_TUESDAY_007', schema 'dbo'.

Fair enough, I updated all my scripts to add explicit execute permissions to our team so that we could use them and of course didn't test because I've assigned execute permissions a bajillion times before.  Yup, it even says so in BOL "To declare a table variable that uses a user-defined table type, EXECUTE permission is required on that user-defined table type."  They deployed my code while I was out of town and BOOM! it defecated all over itself.  

What I supplied was

GRANT EXECUTE ON dbo.CONTRIVED_EXAMPLE TO [Domain\DevTeam]

and what I received was 

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'CONTRIVED_EXAMPLE', because it does not exist or you do not have permission.

That syntax is F'ing right, here's my proc grant statement

GRANT EXECUTE ON dbo.EchoBack TO public

The user defined type name is correct

SELECT ST.* FROM sys.types ST WHERE ST.is_user_defined = 1

I tried it without the schema to no avail, what the hell...  It turns out that the syntax wasn't entirely correct.  Final BOL reference shows the syntax is actually 

GRANT EXECUTE ON TYPE::dbo.CONTRIVED_EXAMPLE TO [Domain\DevTeam]

I also learned (Grant transact SQL link)  that I can update my proc/function statements to be more explicit

GRANT EXECUTE ON OBJECT::dbo.EchoBack TO public

References

http://msdn.microsoft.com/en-us/library/bb522526.aspx BOL:  User-Defined Table Types

http://msdn.microsoft.com/en-us/library/ms187965.aspx BOL:  Grant (Transact SQL)

http://msdn.microsoft.com/en-us/library/ms191291.aspx BOL:  Permissions

http://msdn.microsoft.com/en-us/library/ms174346.aspx BOL: GRANT Type Permissions (Transact-SQL)