I've recently joined a new company and this is my first time working with what I would call Big Data.. We are running Access 2003.
I have been tasked trying to speed up the reporting on a monthly extract that comes from our data warehouse team.. Currently, we append flat file data to our MS Access 'master' database. Its loaded into a single table and currently holds around 5M rows covering 12 months of data.. Its currently sitting at around 1Gb..
My predecessor built an 'acceptable' db from a performance perspective by splitting the down the data in to individual country tables. Reports are then dynamically pointed at the specific Country table that a user specifies when running reports avaliable thru the front end. However, this obviously means the data cannot be analysed across a number of countries..
Users then copy the master db on to their C drives and then analyse it from there.. The users copy the database to their C drives because the performance when running over the shared drive is poor..
The structure of the flat table is as follows:
Its obvious that this needs to be moved to a database server, and I will be driving this forward but thats not going to happen anytime soon..
I want to create a shared environment with some multiuser apps in both Excel and Access for the users, for which I am experienced in..
So, for those of you who work with big data.. what would your approach be on how to optimise this?
If I can do anything with this then the next db I'll be looking at contains 7.5M records and is hitting 2Gb..
I have been tasked trying to speed up the reporting on a monthly extract that comes from our data warehouse team.. Currently, we append flat file data to our MS Access 'master' database. Its loaded into a single table and currently holds around 5M rows covering 12 months of data.. Its currently sitting at around 1Gb..
My predecessor built an 'acceptable' db from a performance perspective by splitting the down the data in to individual country tables. Reports are then dynamically pointed at the specific Country table that a user specifies when running reports avaliable thru the front end. However, this obviously means the data cannot be analysed across a number of countries..
Users then copy the master db on to their C drives and then analyse it from there.. The users copy the database to their C drives because the performance when running over the shared drive is poor..
The structure of the flat table is as follows:
Code:
YearMonth : LONG (4)
MT : TEXT (255)
FT : TEXT (255)
Country : TEXT (255)
Company : TEXT (255)
BD : LONG (4)
BN : LONG (4)
Programme : TEXT (255)
Product : TEXT (255)
Descriptor : TEXT (255)
TC : TEXT (255)
Count : LONG (4)
Amount : DOUBLE (8)
CB : DOUBLE (8)
Fee : DOUBLE (8)
Its obvious that this needs to be moved to a database server, and I will be driving this forward but thats not going to happen anytime soon..
I want to create a shared environment with some multiuser apps in both Excel and Access for the users, for which I am experienced in..
So, for those of you who work with big data.. what would your approach be on how to optimise this?
If I can do anything with this then the next db I'll be looking at contains 7.5M records and is hitting 2Gb..