Learn Power BI- Complete Tutorial for Beginners

 

Learn Power BI- Full Course 2023 -A video of 11 Hours & 400 Other videos

Many of you have asked how they can learn power bi or how they can improve the skills they already possess in power bi. There are over 500 videos on my channel of which around 400 are on Power BI in English and another 50+ are on Power BI in Hindi. With my YouTube channel, I am going to show you how you can structure your learning journey with it.

Below are some useful links to help you download files and install the various software that you need.

1. Download all files from Github
2. Install Power BI
   Install from the Windows store or Download
3. DAX Studio — Download
4. ALM Toolkit — Download
5. Tabular Editor — 2 — Download
6. Power Bi report Builder
   Install from Windows Store or Download
7. Create Power BI ID — Refer to Video
8. In case you do not have an official id— Refer

In response to a request from regular channel viewers, I have come up with a video that aims to give a full beginner’s tutorial to you

The full version of the file For Beginner Tutorial Series You Tube.xlsx is here.

This beginner series will now act as an extension of the Learn Power BI video that I shared above. You can take full advantage of the channel by watching the video and complementing it with the beginner series.

However, some users may find it difficult to get started with the beginner series. If this is the case for you, then you could begin with this series. It has 4 videos, which can help you begin your power bi journey.

Quickly Learn Power BI- Click here to check the playlist

“The beginner series” is also known as the Mastering Power BI series. There are over 200 videos in this series, covering a wide range of topics including basics, visuals, DAX, and Power Query. I am continuously adding new videos to this series, so you will keep getting new content as you progress.

Mastering Power BI: Click here to check the playlist.

Once you have completed 50 videos, you can check the concepts you are interested in further in the Advanced Power BI series, also known as Expertise Power BI. This series of videos covers various use cases of Power BI, as well as how and why you should know about it. There are more than 100 videos in the advanced series and there will be many more in the future.

Expertise in Power BI: Click here to check the playlist.

The next series can help you if you plan on migrating from Tableau to Power BI so that you can get the most out of them.

Tableau Vs Power BI: Click here to check the playlist.

If you know how to replicate some of the code you were doing in SQL in Power BI, this is the series for you.

DAX vs SQL: Click here to check the playlist.

If you want to play around with Direct Query.

Power BI Decoding Direct Query: Click here to check the playlist.

With the Power BI Hindi series, which has more than 50 videos and counting, you will be able to quickly get started with your Power BI Journey if you are a Hindi user.

Currently, for other concepts, you can refer to the above English series.

सीखे Power BI हिंदी में: Click here to check the playlist.

You can to my blogs on the community using this link.

My Medium blogs(50+) can be found here if you are interested.

Make sure you subscribe, like, and share it with your friends.

ElasticSearch Query: SQL Query

ElasticSearch(ES) is a search engine based on Lucene. It provides a distributed, multitenant-capable full-text search engine with an HTTP web interface and schema-free JSON documents. Elasticsearch is developed in Java and is released as open source under the terms of the Apache License.

Just as in my last article, where I gave an equivalent of SQL query in Solr: http://starschemaanalysis.blogspot.in/2017/04/solr-query-sql-query.html, I will do the same for ES. ES provides a lot of flexibility and has an equivalent for complex SQL; we will try to explore some of those here.

It is assumed that you already have installed Elasticsearch and can run JSON Query using Kibana Dev tools or any other methods(Curl,etc..).

Let us start with a table "transactions" with columns-price (number), color (varchar), make(varchar), sold(date).

Insert the sample Elastic data into "transactions" folder. The command to search data is: GET /transactions/_search

1) Select All

SQL Query: Select * from transactions

ES Query: {  "query": {"match_all": {} } }

2) Select a filter. Color: red. There are simpler versions than this but to keep the queries consistent, I have used the following format. Must is alternate of "and" in SQL and will play a role when we have more than one filter.

SQL Query : Select * from transactions where color = 'red'

ES Query: { "query":{ "constant_score" : {"filter":{ "bool":{"must" :[{ "term": { "color": "red" } } ] }} } }}

3) Select using a filter, with more than one value. Color: red, green.

SQL Query : Select * from transactions where color in ('red','green') and

ES Query: { "query":{ "constant_score" : {"filter":{ "bool":{"must" :[{ "terms": { "color": ["red","green"] } },{ "terms": { "make": ["ford","honda"] } } ] }} } }}

4) Multiple filters Boolean AND: "And" in SQL and "Must" in ES

SQL Query: Select * from transactions where color in ('red','green') and make in ('ford','honda')

ES Query: { "query":{ "constant_score" : {"filter":{ "bool":{"must" :[{ "terms": { "color": ["red","green"] } },{ "terms": { "make": ["ford","honda"] } } ] }} } }}

5) Two filters Boolean OR. "OR" in SQL , "Should" in ES

SQL Query: Select * from transactions where (color in ('red','green') or make in ('ford','honda'))

ES Query: { "query":{ "constant_score" : {"filter":{ "bool":{"should" :[{ "terms": { "color": ["red","green"] } },{ "terms": { "make": ["ford","honda"] } } ] }} } }}

6) Date filter

SQL Query:Select * from transactions where (color in ('red','green') and make in ('ford','honda')) and sold between '2014-01-01' and '2014-12-31'

ES Query: { "query":{ "constant_score" : {"filter":{ "bool":{"must" :[{ "terms": { "color": ["red","green"] } },{ "terms": { "make": ["ford","honda"] } } ] ,"filter" :[{ "range" : { "sold" : { "gte" : "2014-01-01", "lte" : "2014-12-31" } } } ] }} } }}

7) Data Aggregation. size:0 has been used to get only aggreated data.

SQL Query: Select sum(price) single_sum_price":, avg(price) single_avg_price from transactions

ES Query: { "size" : 0,   "aggs" : {"single_avg_price": { "avg" : { "field" : "price" }}, "single_sum_price": {"sum" : { "field" : "price" }}}}

8) Group By

SQL Query: Select color, sum(price) single_sum_price , avg(price) single_avg_price from transactions group by color

ES Query:{ "size": 0,"aggs": {"group_by_color": {"terms": {"field": "color.keyword"},"aggs": {"average_balance": {"avg": {"field": "price"}},"sum_balance": {"sum": {"field": "price"}}}}}}

8) More than one Group By

SQL Query: Select make,color sum(price) single_sum_price , avg(price) single_avg_price from transactions group by make,color

ES Query:{ "size": 0, "aggs": { "group_by_make": {"terms": { "field": "make.keyword" }, "aggs": {"group_by_color": { "terms": {"field": "color.keyword"},"aggs": {"average_price": {"avg": {"field": "price"}}}}}}}}

9) A Complex Query: Here we can use formulas like a/b and sum(a)/sum(b). I am using one measure and a constant in place of the other - hence instead of a/b I shall be using a/2. Using different aggegations I am doing sum(A)/count(A). I am also using period label: MTD. We can take use such labels in muti-period query.

SQL Query:Select color,make sum(price) sprice , count(price) cprice, avg(price/2) avgvalue1, sum(price)/count(price) avgvalue2 from transactions group by color,make

ES Query : {"size": 0 , "aggs": { "filter1" : {"filter":{ "bool":{"must" :[{ "terms": { "color": ["red","green"] } },{ "terms": { "make": ["ford","honda"] } } ] }} , "aggs": { "group_by_make": {"terms": { "field": "make.keyword" },"aggs": { "group_by_color": {"terms": { "field": "color.keyword" } , "aggs": {"MTD" : {"filter" :{ "range" : { "sold" : { "gte" : "2014-01-01", "lte" : "2014-12-31" } } }  , "aggs" : {"cprice" : { "value_count" :{ "field" : "price"} },"sprice" : { "sum" :{ "field" : "price"} },"avgvalue1": {"avg": { "script" : {"inline": "doc['price'].value !=0 ? doc['price'].value/2 : 0" }}}}},"avgvalue2": {"bucket_script": { "buckets_path": {"formula_1_1" : "MTD.sprice","formula_2_1" : "MTD.cprice"},"script" : "params.formula_1_1 != 0 ? params.formula_2_1/params.formula_2_1 :0" }}} }} }}}} }

Caveat - I am not an elasticsearch expert. Elastic has provided a comprehensive document on features. You can get more details from their site. If you would like to download the sample data for these queries, please write a comment.

Solr Query: SQL Query

Apache Solr is an open source search platform. It can also be used for analysis of structured data. As a SQL developer, we always look for the equivalent of SQL Query on any other system. Just putting down how a SQL query will look on Solr. It is assumed that you have basic knowledge of Solr and SQL and able to install Solr. All the SQL queries are based on RDBMS. Though Solr is also supporting sql in latest release, we are using method to pass parameters in url

Table Structure used: Sales

city       Varchar , country      Varchar , Qty         Number , datadate   Date , Sales      Number ,Item      Varchar ,id         int, Category   Varchar , Cost      Number

All Solr query are executed on URL. A common URL for all queries is http://localhost:8983/solr/sales/select?indent=on {Indent is optional}

1. Select All

SQL : Select * from Sales

Solr : ?q=*:*&wt=json {wt is response parameter that support csv,json,xml}

2. Selected Field

SQL : Select city , cost from sales

Solr : fl=city,cost&q=*:*&wt=json

3. Filters

SQL : Select * from sales where city = 'BOSTON' or Select * from sales where city = 'New York'

Solr : fq=city:BOSTON&q=*:*&wt=json or ?fq=city: "New York"&q=*:*&wt=json . If you do not give double quotes, it will search for both New and York as City =New or City = York. Use : in solr queries.

4. Filter with Date Range

SQL : select * from sales where city = 'BOSTON' and datadate between '2013-01-01' and '2013-12-31'. You might have to cast date in some of the databases.

Solr : fq=city: BOSTON &fq=datadate: ["2013-01-01" TO "2013-12-31"]&q=*:*&wt=json . fq need to used with each filter. Date based on supported formats

5. Group By: In Solr I prefer stats and facet to do this. In stats you need to mention measure and its Aggregations. You can rename measure and assign tag to create a group of measures. Tags are important to apply different filters on different combinations. To keep things simple, facet allows you to group the data. You can mention the group by columns.

SQL : Select city,sum(sales) sales from sales group by city

Solr : q=*&facet=true&stats=true&stats.field={!tag=a1 sum=true key=Sales}sales&facet.pivot={!key=Group1 stats=a1}city&rows=0&wt=json&indent=on

Here ! is only used with first property inside {}. Later we will also use it to name filter and exclude tag/s to get data of a period. Rows=0 will make sure that you get only aggregated data. If you do not keep rows =0. You will also get base data.

In Solr, we can likewise accomplish group by using below format

Solr : select?group.field=city&group=true&indent=on&q=*:*&wt=json

6. Formula , in v=case you want use a/b or a*b or a*2

SQL : Select city,sum(Qty*cost) sales from sales group by city

Solr : q=*&facet=true&stats=true&stats.field={!tag=a1 sum=true key=Sales func}mul(Qty,cost)&facet.pivot={!key=Group1 stats=a1}city&rows=0&wt=json

7. A complex Query : The SQL and Sol Query are not equivalent. In SQL, we will use union all to append three period data. In Solr we will get three different set of datas. In Solr we will use exclude filter property to achieve this

Sql : select city , sum(sales) sales_2015, sum(cost) cost_2015, null sales_2014 , null cost_2014 , null sales_2013 , null cost_2013 from sales where city = 'BOSTON' and datadate between '2015-01-01' and '2015-12-31' group by city union all

select city , null sales_2015, null cost_2015, sum(sales) sales_2014 , sum(cost) cost_2014 , null sales_2013 , null cost_2013 from sales where city = 'BOSTON' and datadate between '2014-01-01' and '2014-12-31' group by city

union all select city , null sales_2015, null cost_2015, null sales_2014 , null cost_2014 , sum(sales) sales_2013 , sum(cost) cost_2013 from sales where city = 'BOSTON' and datadate between '2014-01-01' and '2014-12-31' group by city

Solr: q=*&fq={!tag%3Df_city}city:("BOSTON")&fq={!tag=P2015}datadate:["2015-01-01"+TO "2015-12-31"]&fq={!tag=P2013}datadate:["2013-01-01" TO "2013-12-31"]&fq={!tag=P2014}datadate:["2014-01-01" TO "2014-12-31"]&facet=true&stats=true&stats.field={!tag=sP2015 sum=true key=Sales_1 ex=P2013,P2014}sales&stats.field={!tag=sP2015 sum=true key=cost_1 ex=P2013,P2014}cost&stats.field={!tag=sP2013 sum=true key=Sales_1 ex=P2015,P2014}sales&stats.field={!tag=sP2013 sum=true key=cost_1 ex=P2015,P2014}cost&stats.field={!tag=sP2014 sum=true key=Sales_1 ex=P2015,P2013}sales&stats.field={!tag=sP2014 sum=true key=cost_1 ex=P2015,P2013}cost&facet.pivot={!ex=P2013,P2014 key=gP2015 stats=sP2015 }city&facet.pivot={!ex=P2015,P2014 key=gP2013 stats=sP2013 }city&facet.pivot={!ex=P2015,P2013 key=gP2014 stats=sP2014 }city&rows=0&wt=json&indent=on

8. Open Item

How to do in solr Select city,sum(sales)/sum(qty) Avg_sales from sales group by city

I am not a Solr expert. There can be better solutions. Refer to Solr documentation.

Create Star Schema from Snowflake Schema

Create Star Schema from Snowflake Schema

Most of the OLTP systems are having snow flake schema and most of the times warehouse is in star schema. Here we will try to convert a snowflake schema into a star schema.

Here we will discuss queries required in Initial Load

Source Tables

1. Sales_header
2. Sales_details
3. Store_master
4. City_master
5. State_master
6. Country_mater
7. Item_master
8. Item_category_master
9. Employee_master
10. Department_master
11. Order_header_status
12. Times_calender_master
13. Line_Status
14. Customer_Master

Few Points

1. Customer_Master and Time_calender_master table are de-normalized tables 
2. For Employee we need to create slow changing dimension and at source there is no reference
3. 
   
4. 
   
5. 
   

Tables Scripts 

   sales_header

create table sales_header
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
created_at date,
updated_at date
);

sales_details

create table sales_details
(
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date
);

Store_master

create table Store_master(
store_id number,
store_name varchar2(255) ,
city_id number ,
created_at date,
updated_at date
);

City_master

create table City_master
(
city_id number,
city_name varchar2(255) ,
state_id number ,
created_at date,
updated_at date
);


State_master

create table State_master
(
State_id number,
State_name varchar2(255) ,
Country_id number ,
created_at date,
updated_at date
);


Country_mater

create table Country_mater
(
Country_id number,
Country_name varchar2(255) ,
created_at date,
updated_at date
);

Item_master
create table Item_master
(
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
created_at date,
updated_at date
);



Item_category_master

create table Item_category_master
(
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date
);

Employee_master

create table Employee_master
(
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
department_id number,
created_at date,
updated_at date
);

Department_master
create table Department_master
(
department_id number ,
department_name varchar2(255),
created_at date,
updated_at date
);

Order_header_status
create table Order_header_status
(
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date
);

Times_calender_master
create table Time_calender_master
(
date_id number,
cal_date date,
cal_week varchar2(255),
cal_month varchar2(255),
cal_qtr varchar2(255),
cal_year varchar2(255),
created_at date,
updated_at date
)


Line_Status
create table Line_Status
(
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date
);

Customer_Master

create table customer_master
(
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date
);

----------------------------------------DW---------------------------------------

Store_Dim

CREATE TABLE STORE_DIM_STG
   (
STORE_ID NUMBER,
STORE_NAME VARCHAR2(255 BYTE),
CITY_ID NUMBER,
CITY_NAME VARCHAR2(255 BYTE),
STATE_ID NUMBER,
STATE_NAME VARCHAR2(255 BYTE),
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(255 BYTE),
CREATED_AT DATE,
UPDATED_AT DATE,
LOAD_DATE DATE,
LOAD_STATUS VARCHAR2(255 BYTE)
   ) ;

CREATE SEQUENCE Store_Dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Store_Dim(
store_unique_id number,
store_id number,
store_name varchar2(255) ,
city_id number ,
city_name varchar2(255) ,
state_id number ,
State_name varchar2(255) ,
Country_id number ,
Country_name varchar2(255) ,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


Item_dim

create table Item_dim_stg
(
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

CREATE SEQUENCE Item_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Item_dim
(
item_unique_id number,
item_id number,
Item_name varchar2(255)
last_purchse_price number,
last_sell_price number,
item_type number,
item_cat_id number,
item_cat_name varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


Employee_dim

CREATE SEQUENCE Employee_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

 create table Employee_dim_stg
(
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
Mgr_name varchar2(255),
department_id number,
department_name varchar2(255),
start_date date,
end_date date,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table Employee_dim
(
emp_unique_id number,
emp_id number,
emp_name varchar2(255);
DOB Date,
DOJ Date,
Mgr_id varchar2(255),
Mgr_name varchar2(255),
department_id number,
department_name varchar2(255),
start_date date,
end_date date,
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Order_header_status_dim

CREATE SEQUENCE Order_header_status_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;


 create table Order_header_status_dim_stg
(
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);
create table Order_header_status_dim
(
Order_header_st_Unq_id number,
Order_header_status_id number,
Order_header_status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Times_calender_dim

CREATE SEQUENCE Time_calender_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Time_calender_dim
(
date_unique_id number,
date_id number,
cal_date date,
cal_week varchar2(255),
cal_month varchar2(255),
cal_qtr varchar2(255),
cal_year varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
)


Line_Status_dim

CREATE SEQUENCE Line_Status_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

 create table Line_Status_dim_stg
(
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);


create table Line_Status_dim
(
Line_Status_unq_id number,
Line_Status_id number,
Line_Status varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

Customer_dim

CREATE SEQUENCE Customer_dim_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

create table Customer_dim
(
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table Customer_dim
(
customer_unq_id number,
customer_id number,
customer_name varchar2(255),
customer_type varchar2(255),
customer_group varchar2(255),
created_at date,
updated_at date,
Load_date date,
load_status varchar2(255)
);

create table sales_fact_stg
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
hd_created_at date,
hd_updated_at date,
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date,
STORE_UNIQUE_ID number,
item_unique_id number,
emp_unique_id number,
Order_header_st_Unq_id number,
date_unique_id number,
Line_Status_unq_id number,
customer_unq_id number
);

create table sales_fact
(
order_id number,
order_date date,
customer_id number,
employee_id number,
store_id number,
order_status_id number,
hd_created_at date,
hd_updated_at date,
line_id number,
order_id number,
item_id number,
line_status_id number,
order_qty number,
item_price number,
item_cost number,
order_value number,
line_discount number,
dis_head_discount number,
created_at date,
updated_at date,
STORE_UNIQUE_ID number,
item_unique_id number,
emp_unique_id number,
Order_header_st_Unq_id number,
date_unique_id number,
Line_Status_unq_id number,
customer_unq_id number
);
--------------------------------------------------------




INSERT
INTO STORE_DIM_STG
  (
    STORE_ID,
    STORE_NAME,
    CITY_ID,
    CITY_NAME,
    STATE_ID,
    STATE_NAME,
    COUNTRY_ID,
    COUNTRY_NAME,
    CREATED_AT,
    UPDATED_AT,
    LOAD_DATE,
    LOAD_STATUS
  )
select sm.STORE_ID,
sm.STORE_NAME,
                    sm.CITY_ID,
                    cm.CITY_NAME,
                    cm.STATE_ID,
                    stm.STATE_NAME,
                    stm.COUNTRY_ID,
                    ctm.COUNTRY_NAME,
                    sm.CREATED_AT,
                    sm.UPDATED_AT,
                    sysdate,
'New'
                   

from STORE_MASTER sm left outer join CITY_MASTER on(sm.city_id=cm.city_id)
 left outer join STATE_MASTER stm on(cm.state_id=stm.state_id)
left outer join COUNTRY_MATER ctm on(stm.country_id=ctm.country_id);


Insert into Store_Dim
select Store_Dim_seq.nextval ,
STORE_ID,
    STORE_NAME,
    CITY_ID,
    CITY_NAME,
    STATE_ID,
    STATE_NAME,
    COUNTRY_ID,
    COUNTRY_NAME,
    CREATED_AT,
    UPDATED_AT,
    LOAD_DATE,
    LOAD_STATUS
from STORE_DIM_STG;
------------------------------------------------------------
Item_dim

insert into item_dim_stg

(
item_id ,
Item_name ,
last_purchse_price,
last_sell_price,
item_type ,
item_cat_id ,
item_cat_name ,
created_at ,
updated_at ,
Load_date,
load_status)
   select distinct

  (    
         im.item_id ,
    im.Item_name ,
      im.last_purchse_price ,
         im.last_sell_price ,
         im.item_type ,
         im.item_cat_id ,
         im.created_at ,
         im.updated_at ,
         icm.item_cat_name,
         icm.created_at,
         icm.updated_at,
         sysdate,
        'New' load_status
 from Item_master im left outer join Item_category_master icm on (im.item_cat_id =icm=item_cat_id );


insert into item_dim
Select Item_dim_seq.nextval item_unique_id,
item_id ,
Item_name ,
last_purchse_price,
last_sell_price,
item_type ,
item_cat_id ,
item_cat_name ,
created_at ,
updated_at ,
Load_date,
load_status
from insert into item_dim;

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

Employee_dim


insert into Employee_dim_stg

(
emp_id ,
emp_name ,
DOB ,
DOJ ,
Mgr_id ,
Mgr_name ,
department_id,
department_name ,
start_date ,
end_date ,
created_at ,
updated_at ,
Load_date ,
load_status
)
select distinct
(
em1.emp_id,
em1.emp_name,
em1.DOB ,
em1.DOJ ,
em1.Mgr_id,
em2.mgr_name,
em1.department_id ,
em1.DOJ,
'31-Dec-2199' End_date,
em1.created_at ,
em1.updated_at,
dm.department_id ,
dm.department_name ,
em1.created_at ,
em1.updated_at
sysdate,
'New'load_status
from
Employee_master em1 join Employee_master em2 on(em1.mgr_id=em2.emp_id)
left outer join Department_master dm on(em1.department_id =dm.department_id )


insert into Employee_dim
select Employee_dim_seq.nextval emp_unique_id ,
emp_id ,
emp_name ,
DOB ,
DOJ ,
Mgr_id ,
Mgr_name ,
department_id,
department_name ,
start_date ,
end_date ,
created_at ,
updated_at ,
Load_date ,
load_status
from Employee_dim_stg
---------------------------------------------------

Order_header_status_dim


insert into Order_header_status_dim_stg
(
Order_header_status_id ,
Order_header_status ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select

         Order_header_status_id ,
         order_header_status ,
         created_at ,
        updated_at,
        sysdate,
        'New' load_status
from
Order_header_status
 );

 insert into Order_header_status_dim
select Order_header_status_seq.nextval Order_header_st_Unq_id ,
Order_header_status_id ,
Order_header_status ,
created_at ,
updated_at ,
Load_date ,
load_status
from Order_header_status_dim_stg
--------------------------------------------------------------
Times_calender_dim


insert into Time_calender_dim
(
date_unique_id ,
date_id ,
cal_date ,
cal_week ,
cal_month ,
cal_qtr,
cal_year ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select distinct
Time_calender_seq.nextval
date_id ,
cal_date ,
cal_week ,
cal_month ,
cal_qtr ,
cal_year ,
created_at ,
updated_at,
sysdate,
'y'load_status

from Time_calender_master);


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

Line_Status_dim



insert into Line_Status_dim_Stg
(
Line_Status_id ,
Line_Status ,
created_at ,
updated_at ,
Load_date ,
load_status
)
(select distinct

Line_Status_id ,
Line_Status ,
created_at ,
updated_at,
sysdate,
'New'load_status
from
Line_Status);


insert into Line_Status_dim
select
Line_Status_seq.nextval, Line_Status_unq_id ,
Line_Status_id ,
Line_Status ,
created_at ,
updated_at ,
Load_date ,
load_status
from Line_Status_dim_Stg
-------------------------------------------------------------------


Customer_dim



insert into Customer_dim_stg
(
customer_id ,
customer_name ,
customer_type ,
customer_group ,
created_at,
updated_at ,
Load_date ,
load_status
)
(select distinct
         
           customer_id ,
           customer_name ,
           customer_type ,
           customer_group ,
           created_at ,
           updated_at,
          sysdate,
            'New'load_status
from Customer_Master
);

insert into Customer_dim
select Customer_dim_seq.nextval customer_unq_id ,
customer_id ,
customer_name ,
customer_type ,
customer_group ,
created_at,
updated_at ,
Load_date ,
load_status
from Customer_dim_stg


----------------------------Sales Fact-----------------------------------------------------
----Keep Original Id from fact ------------------------------

fact

Insert into Sales_fact_stg
(
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
)

select
sh.order_id ,
sh.order_date ,
sh.customer_id ,
sh.employee_id ,
sh.store_id ,
sh.order_status_id ,
sh.created_at  hd_created_at,
sh.updated_at  hd_updated_at,
sd.line_id ,
sd.order_id ,
sd.item_id ,
sd.line_status_id ,
sd.order_qty ,
sd.item_price ,
sd.item_cost ,
sd.order_value ,
sd.line_discount ,
sd.dis_head_discount ,
sd.created_at ,
sd.updated_at ,
std.STORE_UNIQUE_ID,
id.item_unique_id,
ed.emp_unique_id,
ohd.Order_header_st_Unq_id ,
tcd.date_unique_id,
lsi.Line_Status_unq_id,
cd.customer_unq_id

from sales_details sd
left outer join sales_header Sh
left outer join STORE_DIM_STG std on(sd.store_id=std.store_id)
left outer join item_dim id on (sd.item_id=id.item_id)
left outer join Employee_dim on ed (sd.emp_id=ed.emp_id)
left outer join Order_header_status_dim ohd (sd.Order_header_status_id= ohd.Order_header_status_id)
left outer join Time_calender_dim tcd on (sd.date_id=tcd.date_id)
left outer join Line_Status_dim lsi on(sd.Line_Status_id=lsi.Line_Status_id)
left outer join Customer_dim cd on(sd.customer_id =cd.customer_id );


Insert into Sales_fact
(
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
)
select
order_id ,
order_date ,
customer_id ,
employee_id ,
store_id ,
order_status_id ,
hd_created_at ,
hd_updated_at ,
line_id ,
order_id ,
item_id ,
line_status_id ,
order_qty ,
item_price ,
item_cost ,
order_value ,
line_discount ,
dis_head_discount ,
created_at ,
updated_at ,
STORE_UNIQUE_ID ,
item_unique_id ,
emp_unique_id ,
Order_header_st_Unq_id ,
date_unique_id ,
Line_Status_unq_id ,
customer_unq_id
from Sales_fact_stg;

** Scripts not validated, might need correction