"Employees schedule" transition from excel to BI

[PowerdriveX6]

Dear gurus!

As background story. In our company we use excel-based employees schedule.
Column A - days of year.
In 1st row - empoyees names.
In cross of any day and employee - STATE of employee from list:
DO = Days off
TR = Travel to/from work
WS = Work shift
TC = Training course
V = Vacation
In excel it have enough visibility (via conditional formating) and flexibility during fillage by supervisors.

Now the story itself.
I want to include data from this file to the PowerBI to manage/vizualize some additional info along with the state of employee in the day.
For example living places in our fleet of apartments.

So, in my mind i come to the FACT/DIM tables.
Get DIM_DATE as dates table (from A column), get DIM_EMP table as distinct from 1st row transposed.
But i dont catch idea, how to build/relate it to the FACT structure with employee STATE in every day...

And how to vizualize this "like existing excel table" view in BI? I suppose that it should be matrix visual with measure in columns and days in rows.

Thats more, about living places (as additional info) - we have distinct list of living places (2/3 rooms apartments, each one take his own room in every watch) and long-term idea is: how to split employees living places during WS to existing living places without overlapping (when in 3 rooms should not be more than 3 employees in any day).

Too complex idea, huh? But initially i need to understand 1st question about STATES per day per employee.

Link to sample file: https://drive.google.com/file/d/1QMfwbvTNGjdCRmC3u2xY10PR1BmsUc0j/view?usp=sharing

[v-yaningy-msft · Accepted solution]

Hi, @PowerdriveX6 

According to your description, constructing such an employee status table is a complex matter, and if the requirements are stringent Ken involves some knowledge of data design, to structure the data into FACT and DIMENSION tables, you need to follow the star schema principle. The following is a concise plan of action:

1. DIM_DATE table: You got it. Create a `DimDate` table with attributes such as date, day of the week, etc. using column A.

2. the DIM_EMP table, use the first row to create a `DimEmployee` table with attributes such as EmployeeID, Name, Position, etc.

3. the FACT_SCHEDULE table, which is a bit more complicated. You need to create a table that combines daily dates, employees and their status. The table might include a table that begins with the following
  DateKey (linked to `DimDate)
  EmployeeKey (link to `DimEmployee`).
  Status (the status of the employee for the day, e.g. DO, TR, WS, TC, V), with respect to the automatic acquisition of the employee's status, perhaps using the time of day from the clocking system, etc. to determine this

Star schema design link: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

Visualizing Schedules in Power BI

To visualize a schedule with a similar layout to Excel, you can use Matrix Visualization in Power BI. The method is as follows:

1. Drag the Date field to the Row area.
2. Drag the Employee field to the Columns area.
3. drag the State field to the Values area.

For more information about creating and managing relationships in Power BI, see
Creating and Managing Relationships link: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

Managing Living Space Assignments To manage living spaces that do not overlap, you need to create additional tables and logic:

1. the DIM_LIVING_SPACES table, which creates a dimension table for the living space with attributes such as Room ID, Apartment ID, and Capacity.

2. the FACT_ALOCATION table, which creates a fact table that records the living space assigned to employees on a daily basis. The table might include attributes starting with the following
  Date key (linked to `DimDate)
  Employee key (link to `DimEmployee)
  Room key (link to `DIM_LIVING_SPACES)
  Status (make sure you only assign to days with WS status)

You will then need to create a measurement or use DAX to ensure that the total number of employees assigned to the room on any given date does not exceed capacity.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum