FK btw tables at staging level

HI,
I'm trying to build DW & BI project to our company's systems ,
at this stage , we have Oracle database and we want to use it in powerpivot in order to use it later in building the reports and dashboards,
so, we want to transform Oracle database to SQL Server database

the steps that I'm walking through, are as the following :
1- migrating database with data from ORACLE DB to SQL Server. ( done )
2- create FK between tables (since there is no relationships between tables in the original ORACLE DB).
3- Design and build fact and dimensions tables.
4- use POWERPIVOT to load tables from SQL Server and create sample reports (perhaps creating relationships between tables is required in this step).
5- use powerview & powermap tools to create reports, charts and dashboards.

(note that the data is not cleaned, could have nulls and other rubbish data)

--------------------------------------------------------------------------

- are the previous suggested road map is right ? if not , what shall we do instad of it ?
- in the 2nd step previously , is it important to create FK ? since I Faced some problems while trying to create FKs.

Many thanks for replying on my post , in advance .

I'm not clear why you are moving data from Oracle to SQL Server when Powerpivot can read data from many different types of data sources including Oracle. Please can you clarify?

nick_white wrote:I'm not clear why you are moving data from Oracle to SQL Server when Powerpivot can read data from many different types of data sources including Oracle. Please can you clarify?

when I tried to create ORACLE connection at powerpivot , the following error message was appeared :
"Failed to connect to the server. Reason: The 'OraOLEDB.Oracle' provider is not registered on the local machine."
which means that it is required to install the 64 bit client and provider.
as mentioned here : https://community.oracle.com/thread/1043041

and for this installation I need to have an access to Oracle DB server Which is not of my privileges and I can't make any modification on that server.


so, what do you think , please?

You need the Oracle client (free download from Oracle, MS,etc) installed on the machine where you are running Powerpivot. You'll also need an account on the Oracle instance that gives you the necessary select permissions for the data you want, but I'm assuming you already have this as you would have needed this to pull the data into SQL Server

nick_white wrote:You need the Oracle client (free download from Oracle, MS,etc) installed on the machine where you are running Powerpivot. You'll also need an account on the Oracle instance that gives you the necessary select permissions for the data you want, but I'm assuming you already have this as you would have needed this to pull the data into SQL Server

should I install ORACLE DB on my local machine before installing ORACLE Client ?
since I didn't find ORACLE Home path on my local machine !
like the one :\oracle\product\11.2.0\client_64\BIN

You do not have to install Oracle DB on your computer. You need Oracle client so you can connect to Oracle DB. When you install Oracle Client it will create folders by itself. You do need TNSnames file. That file has the information about your Oracle DB host name and port id. You can get TNSnames file from the DBA or from a person who already using Oracle client.

Work with business user to identify business keys on each table. Those keys will help you to create FK and PK in a table.

zoom wrote:You do not have to install Oracle DB on your computer. You need Oracle client so you can connect to Oracle DB. When you install Oracle Client it will create folders by itself. You do need TNSnames file. That file has the information about your Oracle DB host name and port id. You can get TNSnames file from the DBA or from a person who already using Oracle client.

Work with business user to identify business keys on each table. Those keys will help you to create FK and PK in a table.

all that are OK, but we prefer to work with sql server connection on PowerPivot, because sql server more flexible (for us) to create Dimensions and fact Model.
so , the question is :
is it important to create FK between staging tables ? since I Faced some problems while trying to create FKs.

thank you in advance

please , I'm waiting for a reply ASAP ...

It dependence on how you are checking the data integrity (parent/child or Pk/Fk) relationship. If you do not create FK in staging area then you can enforce data integrity via ETL.... look up parent key ( business key) in the parent table when insert rows into a child table. Most of the time staging area gets subset of whole population ( delta or changed records) so data integrity is enforced via ETL.