Link to home
Start Free TrialLog in
Avatar of jknj72
jknj72

asked on

Load Fact table in SQL Server SSIS package

One of the developers here created this process in Oracle that ends up being a query that references 5 tables with joins. What I did was create an SSIS package to load the data into SQL tables. I then created another package to load Dimension tables based off of these tables .

Im currently at the point where I need to load the Fact table. Here is a pic of my tables



I have been doing some research and I found a good example of loading my Fact table and Im having trouble trying to figure out what my first steps are. I realize that I need to do Lookup to get Dimension info but in this example
Demo Im trying to follow

Im having trouble trying to follow the logic in the Fact table Load. The author is starting off using an OleDB Source(Hire table) to get the SnapshotDateKey, which I kinda understand, but he is using a source from an OLTP table? I dont know what I would need to do in my process and if so how would I customize it for my needs. It is throwing me off big time..

Its been a while so if there is something that doesnt seem like it belongs please let me know....

Thanks for your help. I really need to figure this out and wanted to see what developers, who know what they are doing, think about what Ive done so far....
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

source OLTP "main" table it is the core "element" of this process
it is where from data is pumped into DW tables
Once the dimension tables are up to date then, for each fact row look up the surrogate key (your xxxKeys) for each dimension using the business (natural) key for that fact row.  Include the surrogate keys that you looked up that way as the dimension keys of the fact row.
You can use joins in the fact query to do the lookup, or SSIS Lookups if for some reason you are doing the lookups from a different server. The "best" way depends on a lot of things.
Avatar of jknj72
jknj72

ASKER

Where would the source come from though? Should I use the original query but with my Stage tables?
The dimension tables would normally contain both the surrogate keys (as PKs), referenced by the fact table(s), and the business keys needed for lookups at load time. Look up the surrogate key from each dimension using the business key contained in the source fact data.

It's not the only possible way to do it, of course, but it's very straightforward.
Avatar of jknj72

ASKER

So is the first step for me simply to do Lookups on my dimension tables and insert into the Fact? The example I was working off of was starting with an OLEDB source (Hire table) and then doing Lookups? This is where Im confused.
You would update the dimensions first, so that they can be used as lookup tables for the dimension keys in the fact table(s). The dimension surrogate keys are typically IDENTITY columns, and you need to have the data loaded there, in the dimensions, in order for the surrogate keys to be assigned. (Much more complex schemes are possible, of course.)

Once the dimensions are up to date, containing all rows that will be referenced by any new fact data being loaded, you can load the fact table and look up the dimension surrogate keys while loading.  The lookups are performed using the business/natural keys, and they return the surrogate key values.

In more complex designs, such as those that include slowly changing dimensions, the lookup may be more complex but it is still based on the business key, along with possible other qualifiers.
Avatar of jknj72

ASKER

Ok Im just talking about the Fact table load at this point because I have already loaded the dimension tables.

Im trying to find out every component, in my scenario, that I need to load the data into the fact table other than the Lookups...

I feel like Im missing a source.. I have loaded the data tables from Oracle, I have loaded the Dimension tables based off of those tables. Now, Im trying to load the Fact table with this data. I know I have to create a process where the data can be loaded again but I havent done that yet. I have to present the way the data would look to my boss before I get the ok to build a complete process.

In the link provided they are setting an OLEDB source to table Hire and then doing lookups before the insert into the Fact table. The source table Hire is what I am not understanding. Its not a Dimension table and it being used as a source. I am trying to figure out what is my source??

Pleaselet me know if you have any questions...Thanks again
Facts will usually come from a different source than dimensions do, although common business key must exist in all the sources in order to perform dimension lookups. A notable exception would be a "fact dimension," but I'm assuming that that is not what you have in your data.

I see at least two facts, a transaction code count and a transaction error percentage. There is also a transaction date, which might be used as a fact. I'm not sure exactly what's what. Where does that data come from in your transactional system?
Avatar of jknj72

ASKER

The data comes from Oracle database.

In the link I provided I understand everything except the Hire table. I want to know what my Hire table would be?
And that's also what I am asking. It will be the source of the fact data in your transactional system. Without knowing your database, I don't know what the source would be. The facts themselves are transactional: TXN_Code_Count, TXN_Error_Percent. Where is that information found in your database?

I have a lunch appointment right now, and then I will be out of office but probably online and able to respond intermittently. I don't want to just disappear on you.
Avatar of jknj72

ASKER

I appreciate you sticking with me.. Respond when you can, no big deal at all

Our company uses an Oracle database. One of the guys here built a process in Oracle to get Error information from submissions. He created a package with many procedures that build a ton of tables that he fills for reporting. Ive seen the reports and feel that I could do this in a data mart. So I took just one of these reports and I found the SQL that he loads for the report and thought it would be a good idea for me to build a small data mart to accommodate the report. So the first thing I did was use the tables in the query and loaded the entire tables into SQL(this is done). Then I created Dimension tables off of the tables I loaded from Oracle. I created the Fact table with what I thought I would need to create the report.  Although It may not be complete . All I need to do is prove the concept.

As Im typing this I feel that using the query currently being used in Oracle as my source but with the SQL tables??
ASKER CERTIFIED SOLUTION
Avatar of Megan Brooks
Megan Brooks
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72

ASKER

thx for your help