Tag: Data Warehousing

Introduction to DW/BI–My Newest Old Course at Pluralsight

Way back in November of 2010, I published my second course with Pluralsight, Introduction to Data Warehousing and Business Intelligence. Over that time a lot has transpired. While the basic fundamentals haven’t changed, a lot of technology has. In addition, Microsoft’s WideWorldImporters sample provides a great platform for demonstrating many of the concepts.

Additionally, I have to admit that after eight years the slides and accompanying graphics have started to look a bit dated. As a result, Pluralsight asked me to update the course.

I’m happy to announce the new and improved version of Introduction to Data Warehousing and Business Intelligence is now live at Pluralsight!

In case the title didn’t give it away, in this course I cover all the basics of data warehousing and business intelligence. What are facts and dimensions, and what do they actually look like in the database? How should you design a data warehouse?

All new to this course, you’ll see the various tools  from Microsoft to do DW/BI, using the all new Wide World Importers sample database as our platform. You’ll see how to design a database using SSDT, and see how the concepts of DW/BI were implemented.

You’ll also see how SSIS was used to achieve ETL, as well as SSAS to create an analytic cube to do BI. Multiple tools are then explored for reporting; SSRS, Mobile Report Publisher, Excel, and PowerBI.

So, who is this course for?

As a DBA, you’ll be asked to implement new data warehouse projects. The design of a data warehouse is very different from a traditional database, and it’s important to understand these differences. In addition, you’ll be asked to install and configure the data warehouse tools. We’ll talk about the tools included with SQL Server, such as SQL Server Integration Services, Analysis Services, and Reporting Services.

If you are a database designer or developer, you’ll be asked to design these data warehouses. As we said, the design is VERY different from a traditional database. You need to understand these differences in order to implement a data warehouse.

Software developers are interacting with data warehouses on an increasing basis, in order to get additional data for their programs. In the past developers had to create custom interfaces to other systems in order to get additional information needed for their applications. In today’s world developers are being told to get that required data from the central repository of the data warehouse.

Finally, this course is valuable for project managers and business users who seek to understand the systems they can pull data from in order to do self service reporting. By the end of the course users will understand what terms like facts and dimensions mean, and how to effectively use them in your reporting.

If you aren’t already a Pluralsight customer, but are interested, just shoot me an email: free @ arcanetc.com. I can hook you up with a code which will give you 30 days of access to their site, during which you can watch my courses, or any of the courses on Pluralsight. S

Advertisement

Dealing with the Date Dimension Deployment Dilemma

For those of you who have routines that load up a date dimension, you know that it can be a little slow to run. There are times when I am deploying my database project (hey, you are using SSDT SQL Server Database Tools SQL Server Projects aren’t you???) and I want to recreate the database from scratch. While it is a great opportunity to grab another cup of coffee, tea, or read through a fantastic blog like this one, after doing this a few times you wind up with a bad case of caffeine jitters.

I had a date dimension to load today that was particularly challenging. They legitimately needed a couple hundred years worth of dates, plus there were some special calculated columns that needed to be populated. All total it took about 25 minutes to populate, on a reasonably decent sized server. Ouch. So I came up with an alternate solution that I thought I’d share with you, my adoring public (at least is seems like it from the spam comments that appear as love letters lol).

I created a second database project as part of my same solution. You can name it something generic, like “DateData”, or if you are using a date routine specific to each project, you can name it after your project database with something like “Dates” or “DateData” at the end.

This new project has two files. One is the create table script to create the only table, DimDate. The second is a post deployment script, in which I just copied over the original date dimension population script from the original project. I was able to deploy this, waiting the 25 minutes or so for it to populate my date dimension.

Back in the main database project, I deleted the code in the DimDate population post deployment script, and simply put in an INSERT statement to insert the rows in the DateData’s DimDate into the main projects DimDate. Load time was maybe 50 seconds, although it may have been shorter as I looked away for a moment.

The beauty of this is I only had to do a real load of DimDate once, since the date dimension isn’t likely to change during development, or generally at any point. Once your project is complete and on-line, you can even delete the DateData database. Should you need it again you can simply recreate it from the DateData project that was part of your solution. I can now do a full drop and create deployment (aka Publish in SSDT) as often as I want without risking caffeine poisoning or being condemned to staying awake all night doing a Phineas and Ferb marathon (which actually sounds kinda fun, now I wish I hadn’t fixed this issue. Oh well.)

I’m Speaking! SQL Saturday Nashville and PowerShell Saturday Atlanta

Just wanted to let folks know I’ll be doing presentations at two upcoming events.

The first is SQL Saturday #145 in Nashville. That’s this weekend, October 13th. I’ll be giving my “Introduction to Data Warehousing / Business Intelligence” presentation. Here is the slide deck I’ll be using: introtodatawarehousing.pdf

My second presentation will be October 27 in Atlanta at PowerShell Saturday #003. Yep, the PowerShell guys are taking the Saturday concept and kicking off a series of PowerShell Saturdays. This is only the third, but I see many more coming in the future.

At PowerShell Saturday I’ll be presenting “Make SQL Server Pop with PowerShell”. I’ll cover both the SMO and SQL Provider during this session.

Looks like it’ll be a busy October, but I’d hurry as both events are filling up so don’t wait and get registered now!

Intro to DW/BI at SQL Saturday #167 Columbus GA

Today, September 8th 2012, I’m presenting at SQL Saturday #167 in Columbus, GA. My slides for this presentation can be found at:

https://arcanecode.files.wordpress.com/2012/08/introtodatawarehousing.pdf

The shortcuts from the slides are:

 

Thanks for attending!

devLink 2012–Intro to Data Warehousing / Business Intelligence

Today I’m presenting at devLink 2012. My slides for this presentation can be found at:

https://arcanecode.files.wordpress.com/2012/08/introtodatawarehousing.pdf

The shortcuts from the slides are: