CODEWITHSUNDEEP
sql-serverdata-warehousebusiness-intelligence
Frederico Almeida
Frederico Almeida

Reputation: 317

datawarehouse design for different types of reports

I'm a SQL Developer but I'm new to BI and DW process. Was given to me the task of designing a BI process (who gave me this task maybe doesn't understand the importance of a good DW). We don't have any kind of DW done. Instead we have lots of cache tables in our databases.

What is driving me crazy is the fact that the process to get the change tracking required for one report is totally different to the process that aggregate other data points.

We also have a kind of workflow for each project. Sold, In Progress, Completed, Invoiced, and I have to show a report of this evolution, for example: How many "In Progress", "Completed" per week.

One report have to show:

Week|Project   |Cost
1   |P1        |10
1   |P2        |20
2   |P1        |+2
2   |P2        |-1

With the data for this report I can't just aggregate the data to generate a month report because I have the same project repeated for each week. When I want to get the Monthy situation of the company, I need to have the project only once in my data.

Examples of reports:

My question is: What is the best approach to design this? Should I design many DWs, each one for different purposes?

Upvotes: 2

Views: 112

Answers (2)

Niraj
Niraj

Reputation: 1842

A data warehouse is a collections of dimensions, fact tables and data marts. You will have a single data warehouse, and may have multiple dimensions and fact tables inside a data warehouse depending on your requirement. As for as weekly, and monthly progress is concerned you should be required to have your time dimension structured to show this. Time dimension can be used to calculate weekly and monthly totals, and to show the changes in the values over the time periods.

As per details given in your question you need atleast department, project and time dimension to fulfill your requirement.

To Show project workflow evolution, you may have a separate fact table having project code, project status, and project status change date.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

To answer your only actual question:

Should I design many DWs, each one for different purposes?

You should only design one DW.

That DW might contain different tables for each of your different purposes. In a DW it can be ok for multiple tables to have redundant data if it is necessary to aggregate them differently for different purposes.

Upvotes: 1

Related Questions