• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using access to store data for excel dashboard

Oh yeah. That's part of the fun of a project like this. You can put your target data into any table that makes sense.


If the target is like "Every month Sales should be >9000", then you don't need a table in access. You just synthesize that into your dashboard file somewhere.


If the target does change:

Jan 9000

Feb 10000

Mar 11000


then just pop a table into Access with that data. You'll never need to look at it again save for when you build up a query.


This may help you:


http://sqlzoo.net/


It's a fairly robust live tutorial for SQL. You'll want to go through the first 5 sections. It's not like you have to know SQL to do this, but it will certainly familiarize you with it's basic capabilities. That will in turn help you to figure out which parts of this project you want in Access and what parts you want in Excel. Plus the tutorial should only take you about an hour or so to run through (assuming you're not already familiar with this stuff).
 
Thanks Dan. I'm not verse with SQL, so I'm like a beginner, so these tutorials will be very helpful. Will start going through them now.


Next question: Do you narrate somewhere whether in a Word document or in Access itself how your file is structured or was created? For instance do you explain your tables, queries,any macros you created or functions you used? I ask because I want to be in the habit of documenting things I do so persons after me can understand all that I did in creating the file. Do you do it, and do you think it is necessary?
 
Of course!


The tables are pretty self explanatory, but the queries that you pull into your excel file could do with some explaining. Plus your VBA should always have comments unless you're lazy like me.
 
Ok. Glad you agree. Next question: What is the Primary key that you use in your Access Database. I'm thinking that if a table is used to enter each KPI (in my case I would have 30 tables) then I'm not seeing what would be the primary key since the tables wouldn't be related to each other. What says you.
 
Let it randomly generate one for you. Unless you're doing something mega complicated, it's just serialization. It's occasionally useful if you plan to use pivot tables after the fact.
 
Dan: Me again :). So another question for you. In your database do you establish relationships between your tables? This is what is wanted of me. As I mentioned earlier each KPI falls under a balanced score card perspective. For example the KPIs named "Assets" and "Expense" are financial perspectives. "Customer Service Score" is a Customer Perspective. Now what I want is to run a query that will give the monthly values of the KPIs by perspective. So for instance I should be able to see the data relating to the KPIs falling under the Financial Perspective. Using this example the KPIs would be "Assets" and "Expense". I've tried running this query but it does not work.


Let me try to explain how I have set up my database and you can tell me what I'm doing wrong. I first created a list of the KPIs and there attributes. So I have the KPI "Assets" in the first row and then have the columns providing descriptions of the KPI, eg the Objective it is linked to, the balanced score card perspective, the person responsible to update it etc. As you had suggested I also created a table for each KPI. In this table the monthly figures for the KPIs would be updated. In an attempt to create a relationship I then had a column that says name of KPI. This is a lookup from the first table where all the descriptive data about the KPIs are entered. I think I may be going about this the wrong way though, cause the query doesn't work when I have the data like this.


Can you provide any suggestions. Thanks.
 
emmmmmmmmmmmmmmmmmmmmmmmmmm......................


I know.


Can you post the sql of your query? Maybe that will help me see the problem? Also, can you tell me a few samples from your joined fields?
 
Sorry for the late response. See link to file on skydrive:


https://skydrive.live.com/redir.aspx?cid=16d0ed9ee1cb5b75&resid=16D0ED9EE1CB5B75!112&parid=16D0ED9EE1CB5B75!102&authkey=!AJgQ-3IQHwqwpoY
 
Okies.


You're in the right book. You might be in the wrong chapter.


Here's a few questions, so that I know how to help a little better:


-The target values: are those supposed to be entered by the data entry people? Or are those something your manager has already figured out and has on a spreadsheet somewhere?


-Would you tell me more about the scorecard dashboard? of these fields, which ones do you actually want to use on your dashboard? You don't have to use them all. Then again, you can use them all. But it would help to know exactly what needs to come down?
 
Excellent topic and very informative discussions - contributions.


My issue a little of the topic, would using access database as the source data to build the pivot tables be a work around for the issue where "slicers" only work with one data cache?


I have different data sources types i.e. loss runs, revenue, manhours, etc from different sources that are/can be provided in excel format. I'm thinking I could get all the different data into a db and use this as the pivot table source.


Any ideas?
 
Probably. I'm not at all familiar with slicers or their issues.


But if I'm understanding you correctly: I have a couple of projects where I've got multiple queries on one workbook that are all sync'd to play together one way or the other.
 
Dan:


1. For some KPIs teh targets vary by the month, for others they are fixed for the year and do not change.

2. Info for the dashboard:

- name of kpi

- value for the month

- target for the month

- target for the year

- comments

- initials of the person who has responsibility to update it


In terms of the balance score card perspective, the user wants to see the KPIs group by the perspective they fall under "eg sales would be under the finance perspective along with expenditure". additionally, she wants to be able to click on a perspective and see all teh respective kpis for that perspective only.


let me know if you need further clarification.
 
Hi, sorry to revive old thread


I am not into against the idea of of using DB as the data source, but have anyone really tried to use DB to store large amount of data. For instance, I'm dealing with around 2M rows of data and it takes about 1/2 Hour just to run the query in Access. I have a simple yet long query where after doing some testing and conclude that using DB is okay as long as you keep the query simple, but if you cannot, just run the query in the DB software and use the result as source data.
 
Srikandi2000


Have you tried using Power Pivot rather than the SQL method of accessing Access DB's?


Have a read of:

http://chandoo.org/wp/2010/01/08/powerpivot-review/

http://chandoo.org/wp/2013/01/21/introduction-to-power-pivot/

http://chandoo.org/wp/2013/01/28/introduction-to-dax-formulas-measures-for-power-pivot/
 
You must log in or register to reply here.
Back
Top