More on the Data Pilot: Analyzing a Data Source/Database

 

It's time for more data pilot adventures.

I blogged here about the data pilot. The example I used there was for analyzing data in your spreadsheet. You also, of course, have the option of analyzing, using the data pilot, any of your data sources. You'd want to analyze a data source rather than a spreadsheet for many reasons-- perhaps the data is in Access and not in a spreadsheet. Or the spreadsheet is just realllllly big and you don't want to have to open it every time you want to run data pilot on it.

 

Here's my sample data for this blog. Just a nice basic set of data for a week. It's similar but different to what I used in the previous blog and more suitable for demoing the different options. Click to see it bigger.

The first step, of course, is to make sure you have a data source. (Or database—it's all kind of the same thing.) If you already have your data in Access or another database, you'd skip this, of course. However, let's say that this spreadsheet is extremely large and I need to make a database out of it to save time.

Here's what you do to create a database from a spreadsheet; using a different type of data is pretty similar.

1. Choose File > New > Database.

2. In the window that appears, select the last radio button, and Spreadsheet as the type. Click Next.

3. In the next window, just specify where the spreadsheet is that you want to turn into a database. Click Next.

4. Unmark the Open for Editing option and click Finish.

5. Save the database with an obvious name. This is the name you'll be selecting from dropdown lists in OpenOffice.org.

Now you've got a database.

 

To analyze a database, start the same way as usual.

1. Open a new empty spreadsheet and choose Data > Data Pilot > Start.

2. Choose to use a data source already registered with OpenOffice.org. Click OK.

3. Select the data source name, then the sheet name (typically Sheet1) where your data is, and specify Sheet as the type of data. Click OK.

4. Now you're ready to drag fields into the layout area and set options as you have with other analyses. This time, I'm using the Date fields as the quick filter field, so I put it in the Page area. The rest is as pictured.

• I double-clicked the Units Sold field and selected Average. In the results, we'll see the average for each book and store, not the total.
• For options, I'm choosing to identify categories, and to sum rows and columns.

<>

 

Click OK.

 

Here are the results of the setup shown above.

---

Comments

[Razvan Sandu]

Hello,

I use the exact procedure above to analyse some sales figures in a DataPilot. Data is extracted from an Oracle database via ODBC.

The problem is that it works painfully slow, compared to Excel 2003's Pivot Table (it takes a few secomds on Excel, more than one minute In OpenOffice.org).

Can you please suggest a way to speed up things ? Or am I missing something ? In using the latest OpenOffice.org release, 2.1...

Regards,
Razvan

[Brad Rose]

I have heard that Openoffice is still working on the optimization of the product. They are aware of the slowness.

[Luis Gallardo]

I have a database in OOo's Base with about 13000 rows and it takes ages for retrieving the fields. Another thing is that the CPU is busy at 100% for a long period (until it shows the fields on the DataPilot's wizard). I'm using OOo 2.4.1. Any help, tip, trick or idea to improve the connection between Calc and Base?