Excel, Oracle and Sparx EA Part 1 of 5: Retrieving Data

The organization I work for has adopted Sparx EA as our Business Analysis modeling tool of choice and decided to use an existing Oracle repository for all our projects.  We simplified new installations by creating an installation package that automates the install of both the Oracle client and Sparx EA on to each user’s system.

Recently we were debating removing the Oracle Client from the install package since we’ve moved all our users to using the Cloud Services interface, but before we did, I decided to see if I could take advantage of how easy users can install the Oracle Client using this package, not just for use by Sparx EA, but also to use other applications to retrieve data from the Oracle repository our model information is stored in.

After a bit of reading online, and some experimentation, I was able to create a data table in Microsoft Excel 2010 with a live link to the repository.

1.0: Creating the connection to Oracle.

At the moment, I feel like I’m using a bit of a hack to create the connection, so if someone has found a cleaner path, please let me know.

In Microsoft Excel 2010, in a new workbook:

  1. Select the Data Ribbon
  2. Select Get External Data (far left icon for me)
  3. Select From Other Data Sources
  4. Select From Data Connection Wizard
  5. Excel should display a Window with the title Data Connection Wizard asking you “What kind of data source do you want to connect to?”
  6. Select Other/Advanced from the list, and press Next.
  7. Excel should display a Window with the title Data Link Properties asking you to Select the data you want to connect to”
  8. Select the Oracle Provider for OLE DB from the list, and press Next.
  9. Enter the data source name, user name and password.  We created a read-only account for these queries.
  10. Test the connection.  If everything works, then check Allow saving password, and press Ok.
  11. Excel should display a Window with the title Data Connection Wizard asking you to “Select the Database and Table/Cube which contains the data you want.”
  12. For now select T_statustypes, later this will be replaced with an SQL query, but for some reason the wizard will only allow me to select a table at this step.  I picked T_statustypes because it is likely to contain the fewest entries in the database.
  13. Excel should display a window with the title Data Connection Wizard asking you to “Enter a name and description for your new Data Connection file, and press Finish to save.”
  14. Give this connection a friendly name, description, and a unique file name.
  15. Check the check-box for Save password in file
  16. Excel should display a window with the title Microsoft Excel stating “The password is saved without encryption in an Office Data Connection text file, making your data less secure.  Are you sure you want to save the password.”  This is why we created a read-only account.
  17. Select Yes, and then press Finish.
  18. Excel should display a window with the title Import Data (if you see a window with the title OraOLEDB Logon you missed step 15)
  19. Leave everything as is, and press OK.  (OR you can select Properties and jump to step 2.5 below)
  20. If everything worked correctly you should see a two column table in excel, with the first column being Status, and the second being Description.

2.0: Updating the connection to Oracle

Now that you’ve got a connection working, like me, you probably don’t want to simply get the contents of a table as is, so the next step is to update the connection:

  1. Select the Data Ribbon
  2. Select Connections (7 icon from the left for me)
  3. Excel should display a window with the title Workbook Connections
  4. Select the connection you just created, and press Properties
  5. Excel should display a window with the title Connection Properties
  6. Select the Definition Tab
  7. If this is your first time making a change, you’ll need to check the check-box Save Password.
    • Excel should display a window with the title Microsoft Excel stating “The password is saved without encryption in an Office Data Connection text file, making your data less secure.  Are you sure you want to save the password.”  This is why we created a read-only account.
    • Select Yes, and then press Finish.
  8. Change Command type to: SQL
  9. Change Command text to: your SQL query. (see 3.0 for some simple examples)
  10. Press OK
  11. Excel should display a window with the title Microsoft Excel stating “The connection in this workbook will no longer be identical to….
  12. Press Yes.

3.0: Example SQL queries

3.1: A single Element

The following query retrieves a specific Element record from the repository based on it’s GUID.

SELECT *
FROM t_object
WHERE t_object.ea_guid = '{4200F244-23AA-4eed-B70A-9E88CEBC661B}'

You can get an Element or Package GUID by:

  1. Selecting an Element/Package in the project browser
  2. Opening the Properties Window (Alt+1) in Sparx EA
  3. In the properties window there is the group Project, press the plus to expand.
  4. The Element/Package GUID will be listed there.

3.2: All the Elements in a Package.

The following query retrieves all the Elements in a package, sorted in alphabetical order (Project Browser hierarchy is ignored, I will be doing another post on how to maintain hierarchy)

SELECT *
FROM t_object
WHERE t_object.Package_ID = 
(
  SELECT t_package.Package_ID
  FROM t_package
  WHERE t_package.ea_guid = '{0C366767-3DB9-4de5-9CF0-4D2B2E983591}'
)
ORDER BY Name

In my next post I’ll describe how to maintain the hierarchy for the Packages and Elements so they would appear in the same order in Excel as they appear in my Project Browser in Sparx EA.

Leave a Reply

Your email address will not be published. Required fields are marked *