Category Archives: SQL Server

10 SQL Server Data Warehouse Design Best Practices to Follow (Part 1 )

This past Saturday I had the pleasure of speaking at SQL Saturday #552 here in beautiful Jacksonville, Florida. My good friend, Mitch Pearson (blog | twitter) and I presented our session, Designing a Data Warehouse from the Ground Up. We had a great crowd and lots of great questions from the audience!

Watch Designing a Data Warehouse from the Ground Up Webinar Recording

With all the talk about designing a data warehouse and best practices, I thought I’d take a few moment to jot down some of my thoughts around best practices and things to consider when designing your data warehouse. Below you’ll find the first five of ten data warehouse design best practices that I believe are worth considering. This list isn’t meant to be the ten best “best practices” to follow and are in no particular order. Of course, each design scenario is different so you may find that some of the best practices listed here aren’t optimal in your specific situation.

Continue reading 10 SQL Server Data Warehouse Design Best Practices to Follow (Part 1 )

What’s New in Power BI Webinar Recording is Available

Thank you to everyone that attended my webinar with the PASS Excel BI Virtual Chapter today! I’d also like to thank Thomas LeBlanc for having me to present. I had a great time presenting on the newest features of Power BI and what features you will see in the near future. Continue reading What’s New in Power BI Webinar Recording is Available

Microsoft & R Programming Session Materials & Resources Available

Last Thursday night I had the pleasure of speaking with Gregory Kramer and his friends at the Madison, WI Power BI User Group. We took an introductory look at how Microsoft’s acquisition of Revolution Analytics is making serious waves across the Microsoft data platform. We also walked through leveraging R in Power BI and SQL Server 2016.

New to R & Power BI? Start here.

R visuals in Power BI

Continue reading Microsoft & R Programming Session Materials & Resources Available

My Top Four Books for the MS Business Intelligence Professional

As a Business Intelligence Consultant, I do a decent amount of speaking, interacting with the community, and have written and contributed on a few SQL Server books. A question I’m often asked is if I can recommend any good books which brings me to this blog post. I wanted to make you aware of four books for learning data warehousing and  other MS BI technologies that I’ve found incredibly helpful over the years I’ve spent designing and implementing enterprise data warehouse and business intelligence solutions. Continue reading My Top Four Books for the MS Business Intelligence Professional

Learn Designing a Data Warehouse from the Ground Up at SQL Saturday 442 Orlando, FL

image I’m very excited to announce that I’ve been selected to present a session entitled Designing a Data Warehouse from the Ground Up on October 10, 2015 in Orlando, Florida at Seminole State College of Florida! I’ll be presenting this session for the first time along side Mitchell Pearson (b|t). This is going to be an amazing event with tons of amazing, FREE training for everyone including SQL server newbies on up to those who have been in the profession for years. Continue reading Learn Designing a Data Warehouse from the Ground Up at SQL Saturday 442 Orlando, FL

TSQL Script to Find Foreign Key References to a Given Column

It’s always kind of a pain to have to hunt down all those foreign key references so you can address the issues. So I put this script together (based on a script found on StackOverflow) in order to help me find all the required information related to a particular column in a specified table. I’m mostly posting this for my own reference later and for anyone else that may find this useful, so enjoy!

SELECT OBJECT_NAME(f.object_id) as ForeignKeyConstraintName,
    OBJECT_NAME(f.parent_object_id) TableName,
    COL_NAME(fk.parent_object_id,fk.parent_column_id) ColumnName,
    OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName,
    COL_NAME(fk.referenced_object_id,fk.referenced_column_id) as ReferencedColumnName

FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fk 
        ON f.OBJECT_ID = fk.constraint_object_id
    INNER JOIN sys.tables t
        ON fk.referenced_object_id = t.object_id

WHERE OBJECT_NAME(fk.referenced_object_id) = 'your table name'
    and COL_NAME(fk.referenced_object_id,fk.referenced_column_id) = 'your key column name'

Here’s a picture of what the results look like. I ran this query against the ReportServer database used for SSRS in case you were wondering.

T-SQL to find FK key columns

If you want to find every Foreign Key in your database, just eliminate the Where clause to bring back all the FKs. Hopefully you found this as useful as I did.

Generate a Date Table via Common Table Expression (CTE)

Occasionally I find myself needing to generate a small table with a list of dates for various queries I may be running. To do this, I usually leverage the Date dimension since I do most of my work in BI environments with a traditional data warehouse. But if you don’t have access to a Date dimension table, you can quickly generate a date table using Continue reading Generate a Date Table via Common Table Expression (CTE)