AccountingWeb
News
Tutorial: Import Data into Excel from Access Dimensions. Part 1. By David Carter
This is for readers of AccountingWEB who use the Dimensions package from Access Accounting.There are two main ways of getting data out of Dimensions and into Excel. The first is to set up Dimensions as a SQL Server data source in Windows, then use the Import External Data command to pull the data into Excel.
The second is to use Access Analytics, an extra module supplied by Access. Analytics generates two “analysis-ready files” – one for Sales Analysis and one for General Ledger analysis – and feeds them into an Excel pivot table.
We’ll look at Analytics in the next article. But for users who don’t have Analytics, in this first tutorial we’ll set up Dimensions as a data source in Windows and import from the data tables into Excel.
Before we start, a note of caution. SQL Server is a complex beast. If you have a resident techie in the company it would be a good idea to have him/her by your side as you work through this.
1. Pre-start - find your SQL Server name
During the setup, you will be asked for your SQL Server name, so you need to find your server name before you start.
Either: Start – Programs – SQL Server 2005 – SQL Server Management Studio. On my machine it says:
Server Type : Database engine
Server Name : YOUR-CE19999999
Authentication : Windows Authentication
Or: start up Dimensions. At the bottom of my Login screen it says
Database: \\YOUR-CE19999999 : BF
YOUR-CE19999999 is the server name. BF is the user database, which in my case is the Dimensions demo company, Benson and Farrell.
2. Set up Dimensions as a Data Source in Control Panel
Now Start menu again, then Settings - Control Panel - Click on the ODBC32 icon or (Windows XP) Administrative Tools - Data Sources (ODBC).
The ODBC Data Source Administrator box appears.
Switch to the System DSN tab. Add.
From the list of drivers, select: SQL Server
The Create a New Data Source to SQL Server box appears:
Name: Dimensions Description: Dimensions
Server: Type in the name of your Server
Next
The next screen is about authentication. Next to accept the defaults.
At the top of the next screen it should show your user database as the default. If it doesn’t, select yours from the drop-down list. [On my system this list contains master, model, msdb and tempdb. Ignore these as they are system databases, not user databases]
Next.
When the “Change the Language” box appears, click Finish to accept the defaults.
A grey screen appears. Click on Test Data Source. If it’s successful, OK to continue.
You are back at the ODBC Data Source Administrator box. Dimensions has been added to the list of Data Sources. OK to close.
3. Go back into Excel
Start up Excel; open a new workbook. Click onto cell A1. Then:
In the Choose Data Source box, highlight Dimensions. OK.
Hopefully, you now see the Query Wizard – Choose columns box, with a list of tables starting with AA_ACD_DELIVERY_REPRINT.
You are in. These are the data tables for Dimensions, and there’s an awful lot of them!
4. Download a data table
As an example, scroll down to the ORD_HEADER table. Click on the cross next to it.
You see a list of field names, these being the fields which are held in each record.
To start with, bring over all the fields in the file (just click the right hand chevron – all the fields appear in the right hand box.
Next.
The Query Wizard - Filter Data screen appears.
Select INV_DATE. Apply the filter GREATER THAN 1st Feb 2008 (.i.e. only recent transactions). Next.
In the Query Wizard – Sort Order screen, sort on INV_DATE, change Ascending to Descending.
[This means that only recent transactions will come over, with the latest ones appearing at the top of the screen.]
Next. Return the Data to Excel.
5. List the fieldnames via Paste Special – Transpose
Once you’ve downloaded the data table into Excel, highlight the first 4 or 5 rows, then Edit – Copy.
Now go into another worksheet, click on cell A1, then Edit – Paste Special – Transpose.
The field names are copied into column A and are much easier to read.
6. The data is all normalised
Even after you have done a transpose to see the column headings, the data tables will seem pretty incomprehensible. The data you need for reporting is here but it has all been “normalised”, that is, scattered around into numerous data tables.
As the next task, you would now have to “de-normalise” the data all back again, that is, locate all the data fields you need to create reports and re-assemble them into one “analysis-ready” file.
Fortunately for Dimensions users, Access have done that all for them via the Access Analytics module. We’ll look at Analytics in the next article.
6. Download these data tables
In the meantime, if you don’t have Analytics or if you want to learn how your data is organised, it’s time to start digging around in the Dimensions tables.
These tables in particular contain some useful data.
If you are interested in Sales and Margin Analysis
If you are interested in Nominal (GL) Reporting
Related material
David Carter's reporting tutorials and pivot table tips are archived in AccountingWEB's Management Reporting section. Key articles include:
Reporting tutorials index: Use MS Query to extract and analyse accounts data
Reporting Tools #4: Introduction to summary reporting
Towards an analysis-ready file for reporting #1
Towards an analysis-ready file for reporting #2
Management Reporting in Sage Line 50 and Instant
Want to learn more about pivot tables? Start here
Improve your reporting skills with self-teach tutorials
An introduction to Excel-driven reporting tools
Subscribe to the ExcelZone newswire
To ensure you don't miss any of David Carter's Excel reporting tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.
Number of comments: 1
AccountingWEB.co.uk 9-Feb-2008
Categories: IT Features, Management Reporting Features, ExcelZone Features
Times read: 5133
by 2 user(s)
David has import of data into Excel from Dimensions sound very difficult.
Dimensions has a module (office Integration) that is very very simple to use. Access Analytics, to which david refers, is just one part of this module.
Other elements of the module permit the simple extraction of data (and in some cases replacement of modified data). This process is simple and there are even special Access Functions that enable the user to write compex reports very easily.
There is no need for techie involvement - seeting up the Office Integration module is a non-technical operation easily performed by accountants.
