Today I had the opportunity to hang out with a bunch of cool people at SQL Saturday 22 in Pensacola FL. My presentation is SQL Server Full Text Searching, a Guide for Dev’s and DBAs. While I’ve done this presentation in the past, it’s been updated with new material and demos. You’ll find all the code, and the slide deck at the Code Gallery site,
http://code.msdn.microsoft.com/SqlServerFTS
Thanks for coming!
Tonight I’ll be presenting at the Birmingham .Net / BSDA Users Group on Visual Studio Database Edition, aka “Data Dude”. Here is the slide deck in PDF format, sample code will get uploaded in the next day or so.
Just found out our book “SQL Server MVP Deep Dives” is Manning Press Deal of the Day! You can get the book (http://bit.ly/jDoCg ) for $25! Use code dotd0504tw at checkout. This is cheaper than I’ve seen it anywhere else, and you can feel good saving money since all proceeds go to War Child International.
At this price by 2 or 3, after all Mother’s Day is coming up!
Today is April 24, 2010 and I’m in Atlanta speaking at SQL Saturday number 41. I’m giving three sessions today. I guess I’m just a glutton for punishment, LOL.
My first session is an Introduction to Business Intelligence / Data Warehousing. In it I am covering the basics, it’s a true introductory talk where we’ll demystify all the buzz words surrounding Business Intelligence. You can download the slides from here.
My next session is Off and Running with PowerPivot for Excel 2010. Learn the ins and outs of this exciting new tool from Microsoft, see how you can enable your users to do their own Business Intelligence. The slides are ready from this location.
OK, an update before this blog entry even posts, Vidas Matelis just published his step by step guide for getting SharePoint 2010, SQL Server 2008 R2, and PowerPivot all up and going on a single box. (And when I say just, I mean it went up just as I was typing up this post.) Vidas knows a lot about PowerPivot, it’s a great blog to add to your short list. I have a link to his blog in my slide deck, but wanted to pass along a specific link to his install guide, you can find it at http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint .
The final session I’ll be doing is on Full Text Searching. You can download the code samples and slides from my Code Gallery site, http://code.msdn.microsoft.com/SqlServerFTS.
Speaking of Full Text Search, I’ll be doing an Interactive Session at Tech-Ed in New Orleans on Full Text Searching. The session is now in the catalog: http://northamerica.msteched.com/topic/list?keyword=DAT07-INT If you are coming to New Orleans for Tech Ed I’d love to see you there. I’ll also be in the Microsoft Data booth during part of the event, so come on by and say Hi!
I hope to be able to sneak in a few sessions today as well, there will be 49 different sessions at SQL Saturday #41 to pick from (7 tracks, and 7 sessions per track) so it promises to have something for everyone. If you want to follow the fun on Twitter, our official hash tag is #sqlsat41 .
Today I have the privilege of presenting to the internal Regions Bank users group an Introduction to Data Warehousing and Business Intelligence.
The power point slide deck is available for download that this link.
To all who made it, thanks for attending and please let me know if you have any questions.
Just wanted to mention the Pragmatic Works company did a week of free webinars. You can view all of these which cover a wide variety of subjects. The sessions include:
To get access to the seminars, simply go to:
http://www.pragmaticworks.com/resources/webinars/February2010Webinar.aspx
Last Saturday was the Alabama Code Camp, held in Mobile AL. For those unfamiliar with the Alabama Code Camps, we hold on average two a year, and they shift from city to city with different user groups acting as the host group. Other cities include Huntsville, Birmingham, and Montgomery. This time though the Lower Alabama Dot Net User Group under the leadership of Ryan Duclos hosted, and what a great event it was. Everything ran smoothly, there was plenty of drinks and pizza to go around, and some good swag to boot. A big congrats to Ryan and his team of volunteers for a great event, also thanks to Microsoft for sponsoring and the University of South Alabama for the venue.
I was kept busy at this code camp, doing three sessions. The first session was “Introduction to Microsoft PowerPivot”. The slide deck can be found at https://arcanecode.com/wp-content/uploads/2010/01/powerpivot_long.pdf. To see all my PowerPivot posts, simply pick it in the categories to the right or use this link: https://arcanecode.com/category/powerpivot/.
My second session was on Full Text Searching. You can find code samples and the PDF for the presentation at my code gallery site, http://code.msdn.microsoft.com/SqlServerFTS.
The final presentation was an introduction to Business Intelligence and Data Warehousing. Here is the link to the presentations slides in PDF format. As promised in the session I added the additional information for the Kimball Group book.
A quick apology for my delay in posting, a nasty head cold has had me in Zombie land since I got back. Thanks to all who attended, I appreciate you being very interactive, lots of questions, and very attentive. I look forward to the next time Mobile hosts the Alabama Code Camp.
On Monday night January 18, 2010 I will be presenting “SSIS for Developers” at HUNTUG, The Huntsville New Technology Users Group. The meeting kicks off at 6 pm, and we’d love to see you there. Their website has all the location information.
We’ve all been there. We develop a new system to replace some older system long overdue for retirement. The dread always comes around the data conversion step. Having to figure out the quickest, easiest way to get the data from the old system to the new. Or perhaps they have intense data processing that would be better handled by SQL Server, but is too complex for a T-SQL stored procedure. Importing data into their database from an external source, or performing complex data processing over large datasets.
What most developers don’t realize is that they already have an extremely powerful tool to solve both these and other problems: SQL Server Integration Services. SSIS isn’t just for feeding your warehouse, it’s also a great tool for data migration and processing. Come get an introduction and learn how you can be using SSIS for your projects.
The slide deck and main demo can be found at my Code Gallery site:
https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=SSISForDevs&ReleaseId=2883
The calling of SSIS from .Net demo can be found at:
If you remember my Intro to PowerPivot post, you’ll recall that every organization has that power user who leveraged the tools he had on hand, mostly Excel, to do their own form of Business Intelligence. One headache that plagued “that guy” was the freshness of data. It could be quite difficult to keep the data used in his or her spreadsheets up to date. One of the great boons that PowerPivot offers is the ability to quickly and easily refresh data.
Once again let’s return to the Excel 2010 spreadsheet we created in my Combining Data from Multiple Sources in PowerPivot. In this example we’ll put ourselves in the role of a salesperson, creating an analysis for one of our best customers, one Lucy Harrington. Going to the Customer tab in our PowerPivot workbook, we click on the drop down menu for the FirstName column. Clear the “Select All” option then scroll down and only check the first name “Lucy” which should be sufficient to find only the customer we wish.
In looking at her record, we are horrified to see that some nit-wit in data entry had improperly given our best customer the title of “Mr.” even though we know Lucy is not only a “Mrs.” but as a newlywed very insistent on the use of the Mrs. title.
Correcting this is easy enough, in the source system we can go make the fix, or perhaps we bribe the DBA with lunch so he’ll make our data fix a high priority and issue this update command to the database:
UPDATE SalesLT.Customer SET Title = 'Mrs.' WHERE FirstName = 'Lucy'
So now how do we refresh our data in PowerPivot? Easy enough. In PowerPivot on the Home tab you’ll notice a Refresh button in the Connections group. Clicking on it gives us two options, one to refresh just the current table we are viewing, another to refresh all of the tables in our PowerPivot work area. For this example we’ll pick just the Refresh command.
Once you pick Refresh the Data Refresh dialog appears to let you know of its progress. When done it will let you know of its success and you can then click the Close button to close the window. At this point you’ll see some behavior that I can only attribute to the fact that as of this writing PowerPivot is still in the CTP stage. My screen flashed a few times, then when it was done I was on the last tab in my PowerPivot workbook, the CountryInfo tab. Simply click on the Customer tab to return.
A great thing about the Refresh is that it left our filters intact, you’ll see we are still only looking at Lucy’s, and even better we can see the title of our best customer is now corrected:
If you’re using the same sheet from the previous lessons, you will also notice the calculated column we’d put in place, FullName, is still present.
A word of caution.
When using the Refresh feature, be aware that every time you do a refresh you are pulling the entire dataset back, not just the changed rows. Using SQL Server Profile I monitored the Refresh operation from the server side, and found the query issues was:
SELECT [SalesLT].[Customer].* FROM [SalesLT].[Customer]
If you were to attempt this on a large table in your production system during a busy production time it could have severely negative consequences. Imagine the slowdown that would occur if this were executed against a system having hundreds of millions of rows.
When getting into data sets that large, I would advise moving towards a more structured data warehousing environment. This would provide a structure better suited for analysis. If nothing else though, get IT to setup a copy of the production system in a location updated during off peak hours so that analysts could pull massive amounts of data without impacting production systems.
Summary
Taking into account the strain on the source systems, the easy one button refresh does provide a great way for analysts to ensure the data they are making business decisions from is current and up to date.
In my previous post we looked at how Calculated Columns work in PowerPivot. One limitation you may have noticed though was all of the calculations were done using values in that individual table. What if you wanted to lookup a value in second table, based on a value in the first table, and return a value from that second table. Yes Virginia, not only is there a Santa Clause but there’s also an answer for us in PowerPivot’s RELATED function.
In addition to the standard Excel functions, PowerPivot provides a set of its own functions for working with its data. These new functions are collectively known as Data Analysis eXpressions, or DAX for short. By now you’ve probably guessed that the first function from the DAX toolbox you’ll want to learn is the RELATED function.
Let’s start with the same Excel 2010 workbook we had at the end of the lesson Combining Data from Multiple Sources in PowerPivot. If you recall we had imported data from the AdventureWorksLT2008 database. To that we added data the CountryInfo table, which we’d typed in to an Excel spreadsheet.
At the time we used this to get the CountryAbbr column, and you may have wondered why we also included a DiscountRate column. It’s this lesson where this DiscountRate will come in to play.
If you recall from that post, we used PowerPivot’s Manage Relationships feature to create a link that ultimately connected the SalesOrderDetail table to the CountryInfo table. This groundwork enables us to lookup values very easily. Here is a simple example. Go to the Add Column column of SalesOrderDetail and enter this formula into the fx area:
=RELATED(‘CountryInfo'[CountryAbbr])
When PowerPivot gets done, the abbreviation for each country to be shipped to appears in this column. This can be used to “flatten out” some of your data. However it’s much more useful when used as part of a calculation. Delete the column we just added (right click on the column header and pick Delete Column from the menu).
Looking in the CountryInfo table we see the DiscountRate. A value of 0.04 means our US customers get a discount of 4% off their LineTotal. So in our SalesOrderDetail table we want to take the LineTotal and calculate a new value based on the rate, which is stored in the CountryInfo table. Simple enough using the PowerPivot RELATED function.
=[LineTotal] * (1 – RELATED(‘CountryInfo'[DiscountRate]))
This is fairly simple math, we take the DiscountRate from CountryInfo (for US, 0.04) and subtract from 1 giving us 0.96, or 96%. This is then multiplied by the LineTotal giving us our new discounted LineTotal amount, which I renamed to DiscountedLineTotal.
Hopefully these simple examples will give you a glimpse of the immense power RELATED offers. It will allow you to combine data from multiple tables to create in-depth analysis that previously required a specialist in Business Intelligence to create.
PowerPivot has the ability to import data from a wide variety of sources. But you could run across a situation where you don’t have that data stored anywhere. Perhaps it’s on a piece of paper, or in a text file, or it’s just in the user’s brain and needs to be typed in. Logically then you would want to create a new table in PowerPivot.
Except you can’t. PowerPivot itself doesn’t provide the ability to create tables and enter data directly into it. Now, before you start the usual rending of garments and gnashing of teeth plus a little wailing, there is a simple to implement solution.
Create a new Excel 2010 workbook. In sheet 1 (or any sheet) let’s enter the following information.
Now highlight the above cells and Copy them to the clipboard. Next, launch the PowerPivot window by going to the PowerPivot tab in Excel 2010 and clicking the PowerPivot window button.
Once PowerPivot is open, if you look in the middle group of buttons you’ll see a set named Paste from Clipboard The To New Table button should be activated now that you have data in your clipboard.
Click the To New Table button. When you do, the Paste Preview dialog appears.
This is similar to the preview window you see with the Import Table wizard, only not quite as much functionality. Here, we can view the data and validate that it is correct, which it is. We can also indicate if the first row contains our column headers, which in our case it does so we can just leave that option checked on. Click OK to import the data.
Above is our new data, now pasted into PowerPivot. We have the same abilities with it we have with any other table, we can sort, rename our columns, add new calculated columns, and more. As you will note from the tab at the bottom of the picture, the data was pasted into a table with the rather uninformative name of Table. We can do better than that, so right click on the Table tab and pick Rename from the menu. Overwrite Table with CountryInfo.
Now you can see how easy it is to create new data from scratch and paste it into PowerPivot. In this example I used a limited number of rows for illustrative purposes, but it’s quite possible to import massive amounts of data. In addition, you can add to your table later. In this example all we would have had to do is Paste Append from the toolbar.
In the next blog post we’ll build on what we’ve learned and look at how to combine data imported from multiple sources.
What is PowerPivot? Well according to Microsoft:
“PowerPivot is Microsoft Self-Service Business Intelligence”
I can see from the glazed looks you are giving your monitor that was clear as mud. So let’s step back a bit and first define what exactly is Business Intelligence.
Business Intelligence
Business Intelligence, often referred to as simply “BI”, is all about taking data you already have and making sense of it. Being able to take that information and turn it from a raw jumble of individual facts and transform it into knowledge that you can take informed actions on.
In every organization there is already someone who is doing BI, although they may not realize it. Microsoft (and many IT departments) refer to this person as “that guy”. A power user, who grabs data from anyplace he (or she) can get it, then uses tools like Excel or Access to slice it, dice it, and analyze it. This person might be an actual Business Analyst, but more often it’s someone for who BI is not their main job. Some common examples of people doing their own BI today are production managers, accountants, engineers, or sales managers, all who need information to better do their job. Let’s look at an illustration that will make it a bit clearer.
In this example, put yourself in the role of a sales manager. You have gotten IT to extract all of your sales orders for the last several years into an Excel spreadsheet. In order to determine how well your sales people are doing, you need to measure their performance. You’ve decided that the amount sold will be a good measure, and use Excel to give you totals.
In BI terms, the column “Total Sales” is known as a measure, or sometimes a fact, as it measures something, in this case the sales amount. The grand total sales amount is often called an aggregation, as it totals up the individual rows of data that IT gave us. But now you might be wondering why Andy’s sales are so low? Well, now you want to dig deeper and look at sales by year.
In BI terms, the names of the sales people are a dimension. Dimensions are often either a “who” (who sold stuff) or a “what” (what stuff did we sell). Places (where was it sold) and dates (when was it sold) are also common dimensions. In this case the sales dates across the top (2007, 2008, 2009) are a date dimension. When we use two or more dimensions to look at our measures, we have a pivot table.
Now we can see a picture emerging. It’s obvious that Andy must have been hired as a new salesperson in late 2008, since he shows no sales for 2007 and very small amount in 2008. But for Paul and Kimberly we can look at something called trends in the BI world. Kimberly shows a nice even trend, rising slowly over the last three years and earns a gold star as our top performer.
By being able to drill down into our data, we spot another trend that was not readily obvious when just looking at the grand totals. Paul has been trending downward so fast the speed of light looks slow. Clearly then we now have information to take action on, commonly known as actionable intelligence.
So remind me, why do we need PowerPivot?
As you can see in the above example, “that guy” in your company clearly has a need to look at this data in order to do his job. Not only does he need to review it, he also has the issue of how to share this information with his co-workers. Unfortunately in the past the tools available to “that guy” have had some drawbacks. The two main tools used by our analyst have been either Excel, or a complete BI solution involving a data warehouse and SQL Server Analysis Services.
Excel’s main limitations center around the volume of data needed to do good analysis. Excel has limits to the number of rows it can store, and for large datasets a spreadsheet can consume equally large amounts of disk space. This makes the spreadsheet difficult to share with coworkers. In addition mathematical functions like aggregations could be slow. On the good side, Excel is readily available to most workers, and a solution can be put together fairly quickly.
A full blown BI solution has some major benefits over the Excel solution. A data warehouse is created, and then SQL Server Analysis Services (often abbreviated as SSAS) is used to precalculate aggregations for every possible way an analyst might wish to look at them. The data is then very easy to share via tools like Excel and SQL Server Reporting Services. While very robust and powerful solution, it does have some drawbacks. It can take quite a bit of time to design, code, and implement both the data warehouse and the analysis services pieces of the solution. In addition it can also be expensive for IT to implement such a system.
Faster than a speeding bullet, more powerful than a locomotive, it’s PowerPivot!
PowerPivot combines the best of both worlds. In fact, it’s not one tool but two: PowerPivot for Microsoft Excel 2010, and PowerPivot for SharePoint 2010. What’s the difference you ask? Good question.
PowerPivot for Microsoft Excel 2010
PowerPivot acts as an Add-on for Excel 2010, and in many ways is quite revolutionary. First, it brings the full power of SQL Server Analysis Services right into Excel. All of the speed and power of SSAS is available right on your desktop. Second, it uses a compression technology that allows vast amounts of data to be saved in a minimal amount of space. Millions of rows of data can now be stored, sorted, and aggregated in a reasonable amount of disk space with great speed.
PowerPivot can draw its data from a wide variety of sources. As you might expect, it can pull from almost any database. Additionally it can draw data from news feeds, SQL Server Reporting Services, other Excel sheets, it can even be typed in manually if need be.
Another issue that often faces the business analyst is the freshness of the data. The information is only as good as the date it was last imported into Excel. Traditionally “that guy” only got extracts of the database as IT had time, since it was often a time consuming process. PowerPivot addresses this through its linked tables feature. PowerPivot will remember where your data came from, and with one simple button click can refresh the spreadsheet with the latest information.
Because PowerPivot sits inside Microsoft Excel, it not only can create basic pivot tables but has all the full featured functionality of Excel at its disposal. It can format pivot tables in a wide array of styles, create pivot charts and graphs, and combine these together into useful dashboards. Additionally PowerPivot has a rich set of mathematical functionally, combining the existing functions already in Excel with an additional set of functions called Data Analysis eXpressions or DAX.
PowerPivot for SharePoint 2010
PowerPivot for Excel 2010 clearly solves several issues around the issue of analysis. It allows users to quickly create spreadsheets, pivot tables, charts, and more in a compact amount of space. If you recall though, creation was only half of “that guys” problem. The other half was sharing his analysis with the rest of his organization. That’s where PowerPivot for SharePoint 2010 comes into play.
Placing a PowerPivot Excel workbook in SharePoint 2010 not only enables traditional file sharing, but also activates several additional features. First, the spreadsheet is hosted right in the web browser. Thus users who might not have made the transition to Excel 2010 can still use the PowerPivot created workbook, slicing and filtering the data to get the information they require.
Data can also be refreshed on an automated, scheduled basis. This ensures the data is always up to date when doing analysis. Dashboards can also be created from the contents of a worksheet and displayed in SharePoint. Finally these PowerPivot created worksheets can be used as data sources for such tools as SQL Server Reporting Services.
Limitations
First, let me preface this by saying as of this writing all of the components are either in CTP (Community Technology Preview, a pre-beta) or Beta state. Thus there could be some changes between now and their final release next year.
To use the PowerPivot for Excel 2010 components, all you have to have is Excel 2010 and the PowerPivot add-in. If you want to share the workbook and get all the rich functionality SharePoint has to offer, you’ll have to have SharePoint 2010, running Excel Services and PowerPivot 2010 Services. You’ll also have to have SQL Server 2008 R2 Analysis Services running on the SharePoint 2010 box. Since you’ll have to have a SQL Server instance installed to support SharePoint this is not a huge limitation, especially since SSAS comes with SQL Server at no extra cost.
One thing I wish to make clear, SharePoint 2010 itself can run using any version of SQL Server from SQL Server 2005 on. It is the PowerPivot service that requires 2008 R2 Analysis Services.
One other important item to note: at some point the load upon the SharePoint 2010 server may grow too large if especially complex analysis is being done. Fortunately SharePoint 2010 ships with several tools that allow administrators to monitor the load and plan accordingly. At the point where the load is too big, it is a clear indication it’s time to transition from a PowerPivot solution to a full BI solution using a data warehouse and SQL Server Analysis Services.
What does PowerPivot mean for business users?
For business users, and especially “that guy”, it means complex analysis tools can be created in a short amount of time. Rich functionality makes it easier to spot trends and produce meaningful charts and graphs. It also means this information can be shared with others in the organization easily, without imposing large burdens on the corporate e-mail system or local file sharing mechanisms.
No longer will users be dependent on IT for their analysis, they will have the power to create everything they need on their own, truly bringing “self service BI” to fruition.
What does PowerPivot mean for Business Intelligence IT Pros?
The first reaction many BI developers have when hearing about PowerPivot is “oh no, this is going to put me out of a job!” Far from it, I firmly believe PowerPivot will create even more work for BI Professionals like myself.
As upper management grows to rely on the information provided by PowerPivot, they will also begin to understand the true value BI can bring to an organization. Selling a new BI solution into an organization where none currently exists can be difficult, as it can be hard to visualize how such a solution would work and the value it brings. PowerPivot allows BI functionality to be brought into an organization at a low development cost, proving the value of BI with minimal investment. Thus when there is a need to implement a larger, traditional BI project those same managers will be more forthcoming with the dollars.
Second, as users pull more and more data, they are going to want that data better organized than they will find in their current transactional business systems. This will in turn spur the need to create many new data warehouses. Likewise the IT department will also want data warehouses created, to reduce the load placed on those same transactional business systems.
I also foresee PowerPivot being used by BI Pros themselves to create solutions. The database structure of many transactional database systems can be difficult to understand even for experienced IT people, much less users. BI Pros can use PowerPivot to add a layer of abstraction between the database and the users, allowing business analysts to do their job without having to learn the complexity of a database system.
BI Pros can also use PowerPivot to implement quick turnaround solutions for customers, bringing more value for the customer’s dollar. When a BI Pro can prove him (or her) self by providing rich functionality in a short time frame it’s almost always the case they are brought back in for multiple engagements.
PowerPivot also provides great value to BI Pros who are employed full time in an enterprise organization. They can create solutions much quicker than before, freeing them up to do other valuable tasks. In addition PowerPivot solutions can provide a “stop gap” solution, pushing the date at which the organization needs to spend the dollars for a full blown BI solution and allowing IT to plan better.
Finally I see great value in PowerPivot as a prototyping tool for larger BI projects. Now users can see their data, interact with it, analyze it, and ensure the required measures and dimensions are present before proceeding with the larger project.
I’ll reiterate, if anything I believe PowerPivot will create an explosion of work for the Business Intelligence Professional.
Where can I learn more?
Well right here for one. I have become quite interested in PowerPivot since seeing it at the SQL PASS 2009 Summit. I think it will be a valuable tool for both myself and my customers. This will be the first of many blog posts to come on PowerPivot. I am also beginning a series of presentations on PowerPivot for local user groups and code camp events. The first will be Saturday, November 21st 2009 at the SharePoint Saturday in Birmingham Alabama, but there will be many more to come. (If you’d like me to come speak at your group just shoot me an e-mail and we’ll see what we can arrange.)
There’s also the PowerPivot site itself:
I’ve also found a small handful of blogs on PowerPivot, listed in no particular order:
Summary
Thanks for sticking with me, I know this was a rather long blog post but PowerPivot has a lot of rich functionality to offer. While PowerPivot is still in the CTP/Beta stage as of this writing, I see more and more interest in the community, which will continue to grow as PowerPivot moves closer to release. I hope this post has set you off on the right step and you’ll continue to come back for more information.
I’m a big fan of the Kimball method of Data Warehousing. A common task most of us setting up a new Data Warehouse face is creating a Date Dimension. In their book, “The Microsoft Data Warehouse Toolkit With SQL Server 2005 and the Microsoft Business Intelligence Toolset”, they have an example of a good date dimension table in their books sample code. My complaint though was not so much with the layout itself, I liked it and found it fairly complete. Instead it was the method they chose to load it. They used an Excel spreadsheet, then a SQL Server Integration Services package to read the Excel file and load the date dimension table.
To me this approach has a couple of drawbacks. First, if you are doing all the loading on the server itself, you may not have Excel loaded. Thus you may be faced with the headache of creating the sheet then figuring out how to get it to a location the server can read. Second, when you go to add more dates in the future, you have to go into the spreadsheet and reset everything, removing what was there before. It can also be quite a headache to go back several years from know and find both SSIS packages and that Excel spreadsheet. Plus after that time changes may be made to both Excel and SSIS that make that solution no longer workable. Finally quite often it’s a DBA setting up the warehouse, and I’ve found there are still a few DBAs who are uncomfortable relying on SSIS, although I’m happy to say that number continues to shrink.
A T-SQL solution was clearly, to me anyway, the superior answer for both ease of use and long term stability. I assumed that as popular as the Kimball method is, someone would have already created a routine to load their style of date dimension, but some Binging and Googling around proved fruitless. I did find some code for loading some very simple date dimensions, but nothing as complete as the Kimball design. So, relishing a good coding challenge, I rolled up my sleeves and went to work. Below is the fruit of my labor, a script for loading a Kimball like date dimension. All you have to do is set the begin and end dates, indicate the offset for your fiscal year, and let ‘er rip. You can easily go back and add more dates by just adjusting the begin and end times.
A few things you should note. First, I did make a few slight modifications to the standard Kimball date dimension table as found in the previously mentioned book. They have a column titled “DateName” which holds the date as a string in YYYY/MM/DD format. As long as I was putting the date in, I decided to add string versions of the date for the US and Europe. These are in MM/DD/YYYY and DD/MM/YYYY formats and the columns are named “DateNameUS” and “DateNameEU” (for European Union) respectively.
Their table also had an audit key, used presumably by the SSIS package. I didn’t really see the need for an audit key for a date table, so I changed it to an identity column so I could have a secondary surrogate key if I needed it, just something to count the number of date rows easily and track the order they were inserted in.
One final, but very important distinction. I was in a post conference session taught by Erik Veerman at SQL PASS 2009. In it he mentioned using Dim and Fact schemas, thus you’d have [Dim].[Date] instead of [dbo].[DimDate]. I liked the idea as it was something I’d been considering myself, so in this version that is what I did. If you use the more traditional naming format of dbo.DimDate you’ll need to tweak the code.
Below is the code to load the Date Dimension table, which is my creation. Under it I placed my modified version of the Kimball Date Dimension table. It’s core code came from the sample code mentioned in the first paragraph then was modified by me. I include it for completeness.
Update: A few readers aptly pointed out I’d missed replacing a static date field when I worked the final version of the code. Made the change to replace the static date with @DateCounter.
Code Sample 1 – Script to load a date dimension.
/*---------------------------------------------------------------------------*/ /* Loads a Date Dimension */ /*---------------------------------------------------------------------------*/ -- A few notes, this code does nothing to the existing table, no deletes -- are triggered before hand. Because the DateKey is uniquely indexed, -- it will simply produce errors if you attempt to insert duplicates. -- You can however adjust the Begin/End dates and rerun to safely add -- new dates to the table every year. -- -- If the begin date is after the end date, no errors occur but nothing -- happens as the while loop never executes. SET NOCOUNT ON -- turn off all the 1 row inserted messages -- Hold our dates DECLARE @BeginDate DATETIME DECLARE @EndDate DATETIME -- Holds a flag so we can determine if the date is the last day of month DECLARE @LastDayOfMon CHAR(1) -- Number of months to add to the date to get the current Fiscal date DECLARE @FiscalYearMonthsOffset INT -- These two counters are used in our loop. DECLARE @DateCounter DATETIME --Current date in loop DECLARE @FiscalCounter DATETIME --Fiscal Year Date in loop -- Set the date to start populating and end populating SET @BeginDate = '01/01/2008' SET @EndDate = '12/31/2010' -- Set this to the number of months to add to the current date to get -- the beginning of the Fiscal year. For example, if the Fiscal year -- begins July 1, put a 6 there. -- Negative values are also allowed, thus if your 2010 Fiscal year -- begins in July of 2009, put a -6. SET @FiscalYearMonthsOffset = 6 -- Start the counter at the begin date SET @DateCounter = @BeginDate WHILE @DateCounter <= @EndDate BEGIN -- Calculate the current Fiscal date as an offset of -- the current date in the loop SET @FiscalCounter = DATEADD(m, @FiscalYearMonthsOffset, @DateCounter) -- Set value for IsLastDayOfMonth IF MONTH(@DateCounter) = MONTH(DATEADD(d, 1, @DateCounter)) SET @LastDayOfMon = 'N' ELSE SET @LastDayOfMon = 'Y' -- add a record into the date dimension table for this date INSERT INTO [Dim].[Date] ( [DateKey] , [FullDate] , [DateName] , [DateNameUS] , [DateNameEU] , [DayOfWeek] , [DayNameOfWeek] , [DayOfMonth] , [DayOfYear] , [WeekdayWeekend] , [WeekOfYear] , [MonthName] , [MonthOfYear] , [IsLastDayOfMonth] , [CalendarQuarter] , [CalendarYear] , [CalendarYearMonth] , [CalendarYearQtr] , [FiscalMonthOfYear] , [FiscalQuarter] , [FiscalYear] , [FiscalYearMonth] , [FiscalYearQtr] ) VALUES ( ( YEAR(@DateCounter) * 10000 ) + ( MONTH(@DateCounter) * 100 ) + DAY(@DateCounter) --DateKey , @DateCounter -- FullDate , CAST(YEAR(@DateCounter) AS CHAR(4)) + '/' + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/' + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) --DateName , RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/' + RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) + '/' + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName , RIGHT('00' + RTRIM(CAST(DATEPART(dd, @DateCounter) AS CHAR(2))), 2) + '/' + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) + '/' + CAST(YEAR(@DateCounter) AS CHAR(4))--DateName , DATEPART(dw, @DateCounter) --DayOfWeek , DATENAME(dw, @DateCounter) --DayNameOfWeek , DATENAME(dd, @DateCounter) --DayOfMonth , DATENAME(dy, @DateCounter) --DayOfYear , CASE DATENAME(dw, @DateCounter) WHEN 'Saturday' THEN 'Weekend' WHEN 'Sunday' THEN 'Weekend' ELSE 'Weekday' END --WeekdayWeekend , DATENAME(ww, @DateCounter) --WeekOfYear , DATENAME(mm, @DateCounter) --MonthName , MONTH(@DateCounter) --MonthOfYear , @LastDayOfMon --IsLastDayOfMonth , DATENAME(qq, @DateCounter) --CalendarQuarter , YEAR(@DateCounter) --CalendarYear , CAST(YEAR(@DateCounter) AS CHAR(4)) + '-' + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @DateCounter) AS CHAR(2))), 2) --CalendarYearMonth , CAST(YEAR(@DateCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @DateCounter) --CalendarYearQtr , MONTH(@FiscalCounter) --[FiscalMonthOfYear] , DATENAME(qq, @FiscalCounter) --[FiscalQuarter] , YEAR(@FiscalCounter) --[FiscalYear] , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + '-' + RIGHT('00' + RTRIM(CAST(DATEPART(mm, @FiscalCounter) AS CHAR(2))), 2) --[FiscalYearMonth] , CAST(YEAR(@FiscalCounter) AS CHAR(4)) + 'Q' + DATENAME(qq, @FiscalCounter) --[FiscalYearQtr] ) -- Increment the date counter for next pass thru the loop SET @DateCounter = DATEADD(d, 1, @DateCounter) END SET NOCOUNT ON -- turn the annoying messages back on -- Select all rows inserted for the final year as a sanity check SELECT * FROM [Dim].[Date] WHERE DateKey > (YEAR(@EndDate) * 10000)
Code Sample 2 – Modified Kimball code to create a Date dimension.
/* Make sure the Dim schema exists */ IF SCHEMA_ID('Dim') IS NULL EXECUTE('CREATE SCHEMA [Dim] AUTHORIZATION [dbo]') GO /* Drop table DimDate */ IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Dim].[Date]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 ) DROP TABLE [Dim].[Date] GO /* Create table DimDate */ CREATE TABLE [Dim].[Date] ( [DateKey] BIGINT NOT NULL , [FullDate] DATETIME NULL , [DateName] CHAR(11) NULL , [DateNameUS] CHAR(11) NULL --US Date FORMAT, MM/DD/YYYY , [DateNameEU] CHAR(11) NULL --European Union Date Format DD/MM/YYYY , [DayOfWeek] TINYINT NULL , [DayNameOfWeek] CHAR(10) NULL , [DayOfMonth] TINYINT NULL , [DayOfYear] SMALLINT NULL , [WeekdayWeekend] CHAR(7) NULL , [WeekOfYear] TINYINT NULL , [MonthName] CHAR(10) NULL , [MonthOfYear] TINYINT NULL , [IsLastDayOfMonth] CHAR(1) NULL , [CalendarQuarter] TINYINT NULL , [CalendarYear] SMALLINT NULL , [CalendarYearMonth] CHAR(7) NULL , [CalendarYearQtr] CHAR(7) NULL , [FiscalMonthOfYear] TINYINT NULL , [FiscalQuarter] TINYINT NULL , [FiscalYear] INT NULL , [FiscalYearMonth] CHAR(9) NULL , [FiscalYearQtr] CHAR(8) NULL , [AuditKey] BIGINT IDENTITY NOT NULL , CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ( [DateKey] ) ) ON [PRIMARY] GO EXEC sys.sp_addextendedproperty @name = N'Table Type', @value = N'Dimension', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date' EXEC sys.sp_addextendedproperty @name = N'View Name', @value = N'Date', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date' EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Date dimension contains one row for every day, beginning at 1/1/2000. There may also be rows for "hasn''t happened yet."', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date' EXEC sys.sp_addextendedproperty @name = N'Used in schemas', @value = N'Sales (3 roles); Finance; Currency Rates; Sales Quota (2 roles; one at Cal Qtr level)', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date' GO INSERT INTO [Dim].[Date] ( DateKey , FullDate , [DateName] , [DateNameUS] , [DateNameEU] , [DayOfWeek] , DayNameOfWeek , [DayOfMonth] , [DayOfYear] , WeekdayWeekend , WeekOfYear , [MonthName] , MonthOfYear , IsLastDayOfMonth , CalendarQuarter , CalendarYear , CalendarYearMonth , CalendarYearQtr , FiscalMonthOfYear , FiscalQuarter , FiscalYear , FiscalYearMonth , FiscalYearQtr ) VALUES ( -1 , NULL , 'Unknown' , 'Unknown' , 'Unknown' , NULL , 'Unknown' , NULL , NULL , 'Unknown' , NULL , 'Unknown' , NULL , 'N' , NULL , NULL , 'Unknown' , 'Unknown' , NULL , NULL , NULL , 'Unknown' , 'Unknown' ) GO EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Surrogate primary key', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Full date as a SQL date (time=00:00:00)', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FullDate' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Standard Date Format of YYYY/MM/DD', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Standard US Date Format of MM/DD/YYYY', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateNameUS' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Standard European Union Date Format of DD/MM/YYYY', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateNameEU' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Number of the day of week; Sunday = 1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Day name of week', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayNameOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Number of the day in the month', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Number of the day in the year', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Is today a weekday or a weekend', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Week of year', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Month name', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Month of year', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Is this the last day of the calendar month?', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'IsLastDayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Calendar quarter', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Calendar year', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Calendar year and month', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Calendar year and quarter', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Fiscal month of year (1..12). FY starts in July', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalMonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Fiscal quarter', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Fiscal year. Fiscal year begins in July.', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Fiscal year and month', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'Fiscal year and quarter', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Description', @value = N'What process loaded this row?', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'AuditKey' ; EXEC sys.sp_addextendedproperty @name = N'FK To', @value = N'DimAudit.AuditKey', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'AuditKey' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'20041123', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'11/23/2004', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FullDate' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'23-Nov-2004', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..7', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Sunday', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayNameOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..31', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..365', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Weekday, Weekend', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1..52 or 53', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'November', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'Y, N', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'IsLastDayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004-01', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004Q1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1, 2, …, 12', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalMonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'1, 2, 3, 4', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'2004', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'FY2004-01', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Example Values', @value = N'FY2004Q1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayNameOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'IsLastDayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarQuarter' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalMonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalQuarter' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'SCD Type', @value = N'1', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FullDate' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateName' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayNameOfWeek' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DayOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekdayWeekend' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'WeekOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthName' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'MonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'IsLastDayOfMonth' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'CalendarYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalMonthOfYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalQuarter' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYear' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearMonth' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'FiscalYearQtr' ; EXEC sys.sp_addextendedproperty @name = N'Source System', @value = N'Derived in ETL', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'AuditKey' ; EXEC sys.sp_addextendedproperty @name = N'Comments', @value = N'In the form: yyyymmdd', @level0type = N'SCHEMA', @level0name = N'Dim', @level1type = N'TABLE', @level1name = N'Date', @level2type = N'COLUMN', @level2name = N'DateKey' ; GO
I’m currently at the SQL PASS Summit in Seattle Washington. At today’s Birds of a Feather lunch I’ll be acting as host for a table. Our subject is Full Text Searching and FileStreaming. If you’d like to learn more about either of these topics come on by, would love to meet you and see more of how you use these products in your environment.
In addition to help recruit speakers, I also had the privilege of speaking at TechMixer University 2009.
The slide deck and main demo can be found at my Code Gallery site:
https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=SSISForDevs&ReleaseId=2883
The calling of SSIS from .Net demo can be found at:
http://code.msdn.microsoft.com/ssisfromnet
Thanks to everyone who attended TechMixer University. I look forward to seeing you next year!
Arcane Code 