March 27, 2006 at 12:52 am
Guys hi,
although this is a business intelligence originated problem, i put it in T-Sql forum because the solution ultimately regards T-Sql. Please read on, and propose any solution you think is efficient.
Untill the end of October of 2005, my company was outsourcing its MIS system to a company (namely Q) . Untill then, the company Q was exporting ascii files that our MIS system use to load and present the Data. The cooperation ended, my company started to build a new exam-management automated system along with its new MIS system and i was called in to fix the MIS system (that still operates but can not be refreshed and operates with old data of October 2005). I am now fixing the fact tables, but i came across the following problem.
To keep history, my company keeps 5 fact tables fact2005, fact2004, etc. I am now building the new Fact table (fact2006) with data taken from the new inhouse developed exam-management automated system [my company is large educational institute that certifies candidates on specific software (excell, word, cad, etc), by examining the candidates through Tests].
The problem is that the old fact tables have different exam codes and different software codes, than the new system (the programmers that develop the new exam-management automated system, put their own software and test codes). So the dimention tables of tests and software load the same software and test descriptions but with different codes.
This creates a problem in consolidating tests in the dimention tables, since when the fact is executed you can see the same tests or software with two diferrent codes). The simple solution is to create a mapping table, but this means that i have to manually enter 500 rows with - old code, test description, new code - in order to consolidate the data. Is there another way? can you propose a solution?
I would be glad if anyone can help.
Thank you for your time
March 27, 2006 at 2:53 am
Your idea is probably the way I would do it.
The tricky bit is the creation of the mapping table. Perhaps you can partially automate this, by matching on 'test description' between the two systems - should get 80% of the lines out of the way. You will need to validate this, of course, but it should save some typing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 27, 2006 at 3:07 am
Phil, i need your help on this pls.. let me post you an example.
old MIS system - Table Software
soft pk Software_desc software_no(code)
1 AutoCAD 2000i (by AutoDesk) (EN) 201
15 MS Access 2000 (EN) 79
new MIS system - table Software
soft pk Software_desc software_no(code)
1 AutoCAD 2000i (by AutoDesk) (EN) 1
15 Microsoft Access 2000 (EN) 15
I have two issues here.. As you can see not even the description is always same. [Ms in the old system, Microsoft in the new system]. But pls regardless of this, could you please draw me an example of the mapping table?
But regardless of this, could you please draw me an example of the mapping table?
March 27, 2006 at 3:07 am
So are you thinking of adding a new column to the 'old' table corresponding with 'software_no' in the new table?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply