0

As of now in sql server , we have a data and a series normalized table structure that is connected to a C# web app (Human Resource / Recruiting System) (slightly medium (or small 12,000 rows.) size as of now) that does create , read , update and delete.

In the future (2 years time) , we are planning to turn this database into a data warehouse. however in data warehousing normalization in not exactly needed.

My senior programmer(who is not a programmer, but more on business intelligence) suggest that we should denormalize all tables on the same database (Kimball's data warehouse) connect that to the C# web app , do the crud and if test succeeds the data put it in azure data warehouse.

So my main question is this a good thing or is there a better way? like extracting the data from the normalized table and putting it into a new database with the denormalized table? Is there any other way?

Please excuse , I'm more on software development usually using an ORM and not much on business intelligence.

Thank you to All.

Improve this question

1 Answer 1

Reset to default
0

(slightly medium size as of now)

You should be more precise than that when talking about data sizes. Generic words like small/medium/large will mean different things to different people, though in this instance it probably isn't significant.

In the future, we are planning to turn this database into a data warehouse.

You shouldn't turn an application database into a data warehouse - keep the application database optimised for normal live application use and have data fed from it into the warehouse database for offline analysis.

however in data warehousing normalization in not exactly needed.

Correct. Often warehouse solutions are explicitly denormlised in various ways to make certain reports easier to produce efficiently (especially by automated tools and relatively unskilled users).

like extracting the data from the normalized table and putting it into a new database with the denormalized table?

This is generally the way to go.

Keep your live data structures optimised for the applications main workflows and data correctness (i.e. keep things in normal form to avoid data inconsistencies due to design issues or other bugs), and build & update (or rebuild) your warehouse from those application databases using some form of ETL process. The live, normal form, data in the production databases is your source of truth from which you can rebuild the warehouse if something goes wrong. The warehouse should not live in one application instance's database because you may later want to combine data sourced from multiple applications (and/or multiple instances of the same application) in the warehouse, and the differing IO and locking requirements of live applications and large reporting tasks usually mean you want to keep those tasks separate and not competing for resources.

There are exceptions to this of course, as with everything. For instance you may want live reports in the application that you denormalise some data for, for instance, though in this case you would still have core application tables optimised for data consistency and the application's main activity, and the reporting structures built/updated/rebuilt from that source of truth.

Improve this answer
3
  • Thank you for this , You're right , I should have been more specific or clarified the data size , apologies on this. the datasize as of now is currently 12,000 rows with CRUD as the main operation.
    – HelperFunction
    Aug 31, 2018 at 14:10
  • You should add the clarifying information to the question itself (there should be an edit link under the tags list) so people see it without reading as far down as the bottom of this answer. 12,000 rows is decidedly small by most database standards.
    – David Spillett
    Aug 31, 2018 at 14:53
  • Got it , Thank you again for your assistance. I highly appreciate your input.
    – HelperFunction
    Aug 31, 2018 at 15:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.