Can Excel 201+PowerPivot Replace Access?

[bejayrocks]

Hi Folks,


I work as BI for a refinery and in the past the control room systems have been designed on old excel versions, since then we have upgraded to Office 2010, we have learnt to work around the limitations of previous versions of excel but now there is a strong need for data analysis. So as to have access to historical data and facilitate data based decisions I'm looking at migrating entire system to Access DB but still use excel as front end. Have been trolling through the forums, seems like there is a good chance that excel 2010 can double up as DBMS. Is this true? Can I take this risk or am I better off migrating to Access DB. Any guidance would be appreciated.


Thanks

Bejay

 

[bobhc]

Good day bejayrocks


Excel is composed of spreadsheets similar to traditional paper spreadsheets. Each spreadsheet is made up of cells that can be manipulated, and the text in each can be formatted to the user's liking. Access is composed of tables, queries, forms, reports, macros and modules, the last two in Excel as well.

It is difficult to pull information from a single or ranges of cells in Excel as they may be linked to other cells via formulas and fuctions from where they get their cell data, but Excel is the better for data anaylis and presentaions i.e. dashboards and charts. It is also the easyer to use on a basic level.

Access is far better at storing huge/large amounts of data, the amounts of data that would choke Excel. But once set up with user forms and reports Access is very user friendly for entering and extracting data in a whole host of ways. Reports, again when set up can print and/or be sent by email. Queries, forms, and reports can be made a multible number of times to do different tasks and the user can pick which to run from a menu, the user does not need to access to back end of Access and can be fully resticted in what they can and cannot do.

Access data bases need to be planned and set up (pen and paper), trying to add tables on the fly will cause you a great deal of pain and if the table relationships are not set right the data base will become an unworkable mess and you will spend many hours trying to sort it out.

 

[SirJB7]

Hi, bejayrocks!

Consider uploading a sample file (including manual examples of desired output) of older files in Excel and newer converted files, it'd be very useful for those who read this and might be able to help you. And add a description of what kind of data analysis you want to perform, if possible the most complex. Thank you.

Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[bejayrocks]

Hi,


Thanks for your response.


Unfortunately I won't be able to post the workbook as it is quite large. I'll do my best to explain the scenario.


We want to track and trend Efficiency and Utilisation of all critical conveyors in our plant. Our plant works non stop (24X7).


There is a spreadsheet for every conveyor recording production data and downtime reasons.


All I need to calculate efficiency and utilisation is production data and total downtime. But to improve, we need to be able to analyse or drill down on the downtime and different reasons, top 5 reasons for downtimes and more analytical reports for data based decision making.


Since it is a continuous production process and we have more than 50 conveyors, I'm sure you can imagine the flow of data and how it can choke excel. So far we record and analyse one month work of data, back up the spreadsheet and start fresh every month. But if I want to see the trend over months and years, we need to go through a tedious process of manually extracting and analysing data.


Hope this gives some insight to my question

 

[SirJB7]

Hi, bejayrocks!


Thanks for the clear explanation. What would be advisable is to know:

a) how many workbooks do you have?

b) what are the worksheets structure?

c) how many rows (average) do they have?

d) formulas used for calculation of your KPI, don't need to be Excel formulas, just maths

e) how frequently are these workbooks created, updated, and how many rows are added each time?


Just to have a deeper view of what we're talking about.


Regards!


PS: I understand your concerns about the actual data, but I didn't mean to be asking for the real files. If you'd have read the green sticky topics at this forums main page, in this one:

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

you'd have found a workaround, in the sixth paragraph:

"Randomize Numbers and Names if appropriate."

It's a lot of work, sometimes, maybe, but it makes easier for us to try to help you, so it's well worth the pain, isn't it? Otherwise you'd be adding an overload to the contributor's work.

 

[bejayrocks]

a) how many workbooks do you have?


Right Now 1 Workbook with 10 Sheets - 1 Product/Conveyor per sheet, soon there will be 2 more workbooks


b) what are the worksheets structure?


Typical Table Structure (not sure what exact details you are after)


All shets have start time, stop time, time difference, Reasons for Stop, Equipment number, was it planned or unplanned stop or idle capacity.


c) how many rows (average) do they have?


Per month approx 1000 rows per sheet, as I said at the end of the month we back up data, wipe clean and start fresh


d) formulas used for calculation of your KPI, don't need to be Excel formulas, just maths


Difference between Start and Stop Time, thats all is tabulated. Don't store efficiency calculations, even if I do it simply would be


Online Time/Available Time X 100 (as percentage)


e) how frequently are these workbooks created, updated, and how many rows are added each time?


1 row per stop event, as I said earlier haven't seen these sheets cross 1000 rows in a month, at the end of the month we back up data, wipe clean and start fresh


I'm already in the process of rationalizing the DB structure and its contents, but still my concern is will Excel 2010 & Powerpivot be able to handle all data and reporting or should I upgrade to access.


I'll see if I can upload a sample workbook, but won't happen until I'm back at work.


Thanks for your help