0

I'm having some trouble loading a fact table (Fact_Servicio) of a star schema I made in SQL Server, here the diagram:

enter image description here

All the ID's are identity columns.

Our case is the following: we have a Service Desk software which makes daily reports, and we want to use this data to make Business Intelligence, creating a Data Mart (Star Schema) in which we will populate all this data and then display it through Power BI.

Current problem: our issue is in the ETL process with SSIS. After creating the database in SQL Server, we made a SSIS Package to populate all the data from the excel file to this Star-Schema, we start by populating the dimensions, and after this we attempt to populate the fact table Fact_Servicio, but we don't know how exactly to take the ID's of each dimensions, join them with the fields we need to grab from the excel file(which are our previously defined measures) and then insert all in the fact table, we tried using the lookup transformation, but we cannot match any dimension ID with any column in the excel file because these ID's are created in the database and they just autogenerate per record. (The lookup task needs to match the columns we grabbed with columns in the excel file but in the Excel file we don't have any column for ID's, and we wouldn't like to create fields for it because we want to avoid manual tasks as much as possible, because this would be a repetitive task every time we export the data from the Service Desk Software). Here I'm putting some pictures of our SSIS Package structure:

Control Flow View:

enter image description here

Data Flow View of the Fact Table:

Look Up View:

Connection Tab

enter image description here

Columns Tab

enter image description here

Here is where we can't match columns because the ID's are created in the database.

Guys, if there is maybe other way to do this data load, then go ahead and propose how would you do it, otherwise what can we fix here or what transformations from the toolbox can we use. We were also thinking about loading the big excel file sheet to one big single table in SQL Server and work from there but we aren't sure if we would get advantages by doing this.

Thank you all!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jplaudir8
  • 143
  • 1
  • 6
  • 18
  • I have had to do something similar before and the way I accomplished it is to load all the tables like you are, but for the tables that need the ID's added (FK's, lookup IDs, etc) I imported all the data into a staging table, then using SQL in a Stored Procedure I used temporary tables (or added columns to my staging table for the IDs) and did joins from my staging table to the populated tables that contained the ID's I needed and updated the staging/temp table value for the IDs, then once I had all the IDs I inserted into the main table. – Brad May 18 '18 at 19:10
  • Hmm, but then in my case should i create a whole staging database? because all the ID's that i have in the database are created as auto_increment. Or you just mean to create a staging table for the fact table?. also i would like to know if this staging table will have to go in a separate database or if its in the same database should i create a relation with my star schema or not? – Jplaudir8 May 18 '18 at 20:15

0 Answers0