SSIS Novices’ Guide to Data Warehouses: Moving Data into the Data Warehouse. There are two ways that data warehouses often get built. Occasionally, someone in the database trenches pitches the idea to the higher- ups, but more often someone at the top issues a directive to build one. Being the recipient of such a directive can be frightening, especially if you have to quickly build one and you have little or no experience with using SQL Server Integration Services (SSIS). Fear not—I'll guide you in building a data warehouse, while conveying some of the philosophical foundations of data warehousing. However, I won't get into the esoteric topics that seasoned business intelligence (BI) professionals like to discuss when they geek- spar.

Related: SSIS Novices' Guide to Data Warehouses: Flattening While Staging the Data. Providing instructions on how SSIS novices can build a data warehouse is too big to be covered in just one article, so this is the first article in a series. In this article, I'll show you the basic structure of a data warehouse and how to make an SSIS package move data from the source system into that structure. You built a database that enables an online shopping- cart application. Your shopping- cart back end is a SQL Server database named SUBTrxn (short for State. UBags Transactions). When building SUBTrxn, you paid careful attention to getting the data into third- normal form.

You applied the most genius keys and indexes, so SUBTrxn has solid referential integrity. You regularly partition and archive old sales data to keep the tables small and light. Related: SSIS Deployment Strategy for SQL Server 2. Besides being the database developer and DBA, you're also the point person for providing reports. You hate this responsibility because you're constantly bogged down by one- off requests for data sets. You often have to resurrect data from backups, which is so cumbersome that report requesters receive their data sets long after they would've been useful.

You have a folder full of SQL code that you tool and retool constantly in an effort to speed up your report- writing activities. You can't remember how most of your stored procedures work, but they seem to work well enough. You're constantly tiptoeing around land mines in the result sets by explaining the very valid reasons why the measures in one report don't match the measures in another. You hope no one with any SQL expertise ever sees the one stored procedure you rely far too heavily on—the one with the nested subquery pulling from a view that pulls from a view. Recently, the boss of your boss's boss sent word down the command chain that he'd like you to build a data warehouse. You're thrilled, yet scared since you're not experienced with using SSIS.

SmartKey SQL Password Recovery can easily help you reset lost or forgotten user and administrator passwords from MS SQL Server 2012/2008/2005 password-protected.

The only requirement you've been given is . After you complete it, a contractor will build a SQL Server Analysis Services (SSAS) cube on top of it. You have only two weeks to build it.

How do you go about building the database portion of a data warehouse? Bran Van 3000 Garden Download. I'll walk you through the steps using a SQL Server 2.

If you're inexperienced with using SSIS, creating a data warehouse might seem like an impossible task—but it's not if you use some different strategies. For example. The #1 SQL Server community and education site, with articles, news, forums, scripts and FAQs.

R2 machine. Before you start, though, it helps to have a simplified representation of the current pre- warehouse environment. As you grow the current environment into a fully operational data warehouse, you'll be able to refer back to this representation as the starting point. Figure 1 shows the current environment for State. UBags. As you can see, data is being exchanged between the SUBTrxn database and the website that the customers use to view and purchase products, as well as view their purchase history. The back end uses slow, fragmented SQL scripts and stored procedures to output ad- hoc reports. I'll refer to these sets as the Raw tables, Stage tables, and Dimensional tables, respectively. The Raw tables house near- exact copies of the source data (in terms of both the data's structure and values) from the transactional or other external system.

As a general rule, data warehouse developers try to make the Raw tables exact copies. Data warehouse developers should only depart from this rule when there are compelling and defensible reasons to do so. What constitutes compelling and defensible? The burden of proof rests on the developer who made the decision.

So, make your Raw tables as . The data in Stage tables significantly differs from that in the Raw tables in terms of the data's structure values. When you move the data from the Raw tables to the Stage tables, you apply business rules, flatten two or more tables into one, mark records for filtering, or perform other activities.

In other words, anything you need to do to make the data ready for the data warehouse, you do during the move from Raw to Stage. The Dimensional tables store the data long- term in a format that's tailor- made for use by SSAS cube files. Dimensional tables can be fact tables or dimension tables that come together in star, snowflake, and constellation schemas.

For the example used in this article, you need to create two new databases—SUBStg and SUBDW—as seen in Figure 2. The SUBStg database will contain the Raw and Stage tables. Placing the Raw and Stage tables in the same database is often practical, especially in the early stages of development when the warehouse is still small enough for that to be feasible. As your data warehouse grows, you might need separate databases for the Raw and Stage tables. You might even want to have these databases on separate servers.

The SUBDW database will contain the Dimensional tables. These fact and dimension tables will be structured expressly for serving up data to be used by SSAS cubes. First, you need to confirm that you have SSIS installed on your machine. In Control Panel, select Administrative Tools, click Services, and look for SQL Server Integration Services in the list. You'll be using an SSIS package—specifically, an extraction, transformation, and loading (ETL) package—to create the tables in the data warehouse. Second, you need to make sure that you have Business Intelligence Development Studio (BIDS) installed. Choose All Programs on the Start menu, click Microsoft SQL Server 2.

R2, and look for Business Intelligence Development Studio in the list. If you don't find BIDS listed, you'll likely need to re- install it from the SQL Server installation DVD. You'll be using BIDS to edit the ETL package. I'll refer to this ETL package as the Raw package.

You can use the Import/Export Wizard in SQL Server Management Studio (SSMS) to create the Raw package. Although this isn't a common practice, it's a useful method to know even if you decide not to use it much in the long term. It provides an easy- to- learn, reliable path into SSIS development. In SSMS, locate your source database (in this case, the transactional database) in the Object Explorer pane. Right- click that database, select Tasks, and choose Export Data to bring up the Import/Export Wizard.

You've probably used this wizard to move data around in your databases, but you might not have saved your work as an SSIS package. When the wizard appears, you might see an informational screen. If you do, click Next to proceed to the first configuration screen.

Following the guidelines shown in Table 1, complete the first three configuration screens. On the fourth configuration screen titled Select Source Tables and Views, you'll be presented with a list of all the tables in the database you chose as your source database. When you select a table's check box, the wizard fills in the Destination column with a table that has the same name.

As Figure 4 shows, you can change the existing table's name to the name of a table that doesn't yet exist. The wizard will then create the new table for you and populate it with data from the source table. For this example, you need to add the word raw to the beginning of each table name.

Prepending the Raw tables' names with raw and the Stage tables' names with stg is an easy way to keep the two types of tables separate when they're stored in the same database. Try to select at least 1.