Page Comparison

If you are an administrator with Databases and Sync access, you can use Designer to design and build a new Phocas database from scratch. You can also modify existing databases.

As every company is different, there is no real 'right' or 'wrong' way to design a database. We suggest you follow our tips and then build your design, as you can go back and revise your database at any time after it has been built.

Before you start - some tips

Before you jump into creating a database, it is worth spending a few minutes to consider the following tips, as it will likely save you time later on.

Identify your data source

What data do you need and where does it live? Identify your data sources and check the quality of the data. You'll get better results if your source data is as 'clean' as possible.

Think about the users

The design of your database affects how your team can use Phocas. To ensure your database is user-friendly, take into account the needs and skills of your users, the required data structure and naming conventions. Also decide on who you want to be allowed to see data. 

Keep it simple

If you're a beginner at designing databases, make your first one simple. Once you have your first successful build, add more databases. Depending on your Phocas environment, you might have access to one or more simple database designs to help you get started.

Understand the database basics

Ensure you have a good understanding of the key terms associated with databases and how they fit in with Phocas Designer. See Take a tour of Designer.

Create a database

1. Use one of these methods to open the Create Database window:

   • Click the Phocas menu button > Databases > New Database.

   • Click the Phocas menu button > Administration > Databases > Create.

2. Enter a name for the database.

3. If you are creating a financial database, select the Financial Database checkbox.

4. Click Save.  

   Image Removed

   The Design screen opens, where you can proceed to load your raw data and ‘design’ the database. See Take a tour of designer to get an overview of this screen.

   Image Removed

Add data to the database

Is each file a stream? do you need multiple files/streams? When you create a brand new database, why do you see data files in the list? Files are from all databases?

When it comes to adding data to your database, you have two options:

• Upload a static file - Static files are files that do not update, as they are not connected to a database or database view. The acceptable file types are CSV, TXT, XLS and XLSX. Why would you choose this option then?

• Use a sync file - Sync files are files that have been uploaded via the Phocas Sync tool. Sync files are connected to a database and, as the name suggests, synchronized data updates occur at scheduled times. This method is suitable if you want to give users access to the latest data, which is updated regularly (overnight, weekly and so on).

Learn about the data source panel

On the database Design screen, click the arrow at the right to expand the data sources panel.

Image Removed

The the data sources panel displays the Upload, Refresh and Sync buttons (if you have access) and a list of data files.

Uploaded files appear in the section under your user name in the list. I don't see any

Synced files appear under the Sync data source heading in the list. No such ‘heading’

Old image:

Image Removed

Preview the data

why?

Click on a file name to open a preview of the data.

Image Removed

Preview window showing 'Budget' contents. You can click through to view more pages of data. The total number of rows appears in the bottom right of the screen.

Image Removed

Close preview window.

Upload a static file

Use this method to upload a static data file (CSV, TXT, XLS or XLSX ).

1. Prerequisite: Check that your source data is as 'clean' as possible. If you are uploading an XLXS file, the following notes apply: do they still apply?

   • Multiple worksheets display as separate data items.

   • You need to include a header row.

   • You can only upload files that are less than 100MB.

   • Worksheets can be updated later by uploading another file. 

2. On the database Design screen, click the arrow at the right to expand the data sources panel.

3. Click Upload, then select the file, enter a name for the file, select whether it has a header row and click Upload.

   Image Removed

   Your uploaded file displays in the data sources panel. You can delete it from here, if required. 

Select/use a sync file

Select a file how?? that has been uploaded via Sync. See Using Sync.

Configure the database

After creating the blank new database and uploading data, you need to bring that all together by configuring the database. Firstly, you add streams, then map the data in those streams to the components in the database. You can also customize the database components to meet your organization’s needs.

Add streams

Start with your raw data. Typically data this is comprised of transaction files which include data about something that has occurred on a particular date, such as sales to customers, orders to suppliers and so on. does this belong to above section about adding data? how is it different?

maybe need other intro

1. Drag a file from the data sources panel and drop it into the Drag here box in the stream panel, as illustrated below. 

   Image Removed

2. Filter the data being loaded into a stream: Optional? or always done?

   1. Click the filter button next to a raw data file, as shown.

      Image Removed

   2. Click the green plus icon to add one or more filters, as shown. In this example, we only want to include transactions with a value greater than $10, and we have removed any transactions involving the customer USPA_136-004.

   Image Removed

Map the stream components to the database components

Customize the database components

1. This name should be user-friendly, as this is name that displays in Phocas. You can change this name later. The real, underlying database name is generated automatically and will be something like company name, an underscore and a number. This can't be changed and is not displayed to typical Phocas users.

2. If you are creating a financial database, select the Financial Database checkbox*. Please read the Design a finance database for important information.

3. Click Save. 

   Image Added

   The Design screen opens on the Design tab.

   Image Added