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.
The following information uses a non-financial database as an example. See also Add a budget stream.
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
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.
Enter a name for the database.
If you are creating a financial database, select the Financial Database checkbox.
Click Save.
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.
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.
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:
Preview the data
why?
Click on a file name to open a preview of the data.
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.
Close preview window.
Upload a static file
Use this method to upload a static data file (CSV, TXT, XLS or XLSX ).
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.
On the database Design screen, click the arrow at the right to expand the data sources panel.
Click Upload, then select the file, enter a name for the file, select whether it has a header row and click Upload.
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
Drag a file from the data sources panel and drop it into the Drag here box in the stream panel, as illustrated below.
Filter the data being loaded into a stream: Optional? or always done?
Click the filter button next to a raw data file, as shown.
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.
