DB Dev

[gennaroalpha7]

Hi All -

We have a legacy flat file MIS that is used for report generation. We want to develop a SQL relational DB. My questions please...

1. What are the steps/deliverables to develop this DB - I am a Business Analyst on the team?

2. What do I need to collect to develop the DB?

Thank you for your expertise and time.

Alpha

 

[Olaf Doschke]

You're in general D B asking a very general question, so that could work out, but you'll not get general instructions, you'll need to make some decisions, first.

Also, what is that MIS file, is that it's file extension? Or is that just it's name, as in Management Information System?
Typical flat files are CSV and you typically don't work on such a file for a very long time (legacy).

Besides the exusting file(s) the decision on what database must be made by taking into account goals and environment.
What OS?
How much Data?
How many Clients?
How many Users?
Nature of the Data?
Usage of the Data?
Endpoints? What devices?
Do you need backward compatibility or is the legacy system replaced?
What in case the transition has a longer roadmap?

All this might be over the top if we just talk about small flat files, but are you sure you're looking at the current legacy system db or just some query output for a report, a tiny fraction of the data?

In general after deciding which db by the intent and needs you need to get licenses per server/client/user/connection/cpu core depending on the db and business model of the db vendor and then you need data migration, obviously. That can be as simple as a bulk load of a csv or the database design and migration of the data by a DB developer.

Bye, Olaf.

 

[djj55]

There is also the disaster recovery aspect of a system. Since you state this is for reporting, database backups may be enough, however you need to think about what would happen if the database was lost.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[johnherman]

One point that hasn't been addressed here (yet) is whether you just want to put the file into the database with the same structure, or whether you want to take greater advantage of the DB capabilities by modeling the data. Data can be modeled relationally or dimensionally, and a combination of both is certainly possible.

Creating and using a data model can also save on resources, as items like Customer Name would be stored only once in the database and then referenced by a key value. For instance, if one of frequent customers is "New York Stock Exchange", then several bytes are saved each time that Customer Name occurs within the flat file. However, disk is cheap, so that's not a major concern.

Switching to a database will also allow the use of off-the-shelf query tools, but those tools will only function optimally if a data model is used. A data profiling tool can be used to analyze the flat file data, and will be a big help when creating a data model.

If you decide to go with the data model, you can still keep the flat file records in their own database or table. As I mentioned above, disk is relatively cheap.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[gennaroalpha7]

Thank you all for your submissions...

We want to build a SQL server relational database that will be more helpful than the legacy (flat file) Management Information System that we have now. We will need to migrate files over to the new DB. The goal is to modernize and make it easier for our customers to pull their own reports through a reporting tool - such as MS Reporting services...

Please provide steps or best practices to do this...i.e., tips.

Thank you for your help.

Alpha

 

[Andrzejek]

You may want to read on Fundamentals of Relational Database Design

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[johnherman]

In addition to Andy's suggestion, I suggest you directly load the flat file into a SQL Server table and keep it. That can be your archive and someplace to validate your new relational database against. As I mentioned, there are several data profiling tools that can analyze your data and give you a "head start" on the relational design. There may even be some free or nearly free profiling tools. In the older days (late 1990s), the profiling tools were expensive.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[gennaroalpha7]

Thank you everyone! I will take your suggestions...

Alpha