Design your database

Now you are ready to design a simple database using your synced data.

Getting to database admin in Phocas

Go to the Phocas menu  and select Administration > Databases. The databases management screen lists any databases already created. It also shows any blank designs that have been added at implementation, and shows whether any of the databases are part of a 'solution'. 

For example, in the image below:

• Two databases ('Sales' and 'Inventory') have been built.  The 'Solutions column' shows that the 'Sales' database is part of the 'Standard Sales' solution. If a database was installed as part of a solution you cannot change its design or delete its components (eg, dashboards).
• A design for 'Sales and stock' has been loaded during implementation. Because it has not yet been built (and contains no data) its status is 'unavailable'. You can open this design and map your own data or start from scratch by choosing Create.

Start your design

In this step you either:

• Select Create to start from scratch (the most common starting point), OR
• Open a pre-loaded database design and drag in your data.

Option 1: Starting from scratch

Give your database a title, or display name. The display name should be user-friendly (e.g., 'Sales'), as this is name that appears in Phocas. You can change this name later. The actual 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.

Save and the database will automatically open in Designer.

Option 2 (if available): Open a pre-loaded design

Some Phocas sites will have one or more 'empty' database designs pre-loaded. These designs provide a typical basic structure, into which you simply drag your data. To open a design, click the name. This opens a summary screen, shown below, where you can change the title, or display name. 

Select Design to start designing your database.

Typical database elements

The design screen, shown below, has two tabs: Design and Build. It opens with the Design tab active. The screen displays the typical elements of a database: Streams, dimensions, properties, measures and date.

The streams section in the bottom part of the screen is where you first drag your transaction file or files, e.g., 'Sales', 'Budget' etc. 

The top part of the screen is where you map the various columns of data to their structure in Phocas: Dimensions, properties, measures and date.

If you are using a pre-loaded design there will already be some 'placeholder' items under the 'Dimensions and 'Measures' sections, as shown below. These guide you when you start mapping items from your transaction file.

Add data, starting with a transaction file

A transaction file, e.g., sales, should always be the first data source you add to your design. A transaction file typically contains data about an activity that has occurred on a particular date, such as sales to customers, orders to suppliers, etc.

Expand the panel at the right of the screen to view the available data sources. Here you will see the data you added during the previous step ( Load data into Phocas using Sync). You can click on a file name to preview a file. 

Click the arrow to hide the panel. You can pin the panel open.

You can also upload a static data file from here.

Select the transaction file you want to use (e.g. 'Sales') and drag it into your design and onto the 'New stream' tab. 

Map your data  

When you have dragged your transaction file into Designer, you can start matching columns of data to their relevant position on the screen.

Each column of unmapped data is marked by a red dot. Once you drag and drop each column heading into the appropriate section the dots turn blue. Note that you're not restricted to what's already there. You can go ahead and drag more columns into the dimensions, properties or measures sections as required. 

If you make a mistake you can remove the item using the 'X'. You can rename the item and change other details using edit . In fact, you can come back into designer and modify the design even after you build. Sometimes, the best way to see if you've nly way to really tell if what you;ve done 

In the image below, the arrows show where the columns of data have been dragged under the following sections: Dimensions, measures and date. Properties are yet to be mapped. These can be dragged across now, or later, depending on how much additional information you need in this first database.

Note about dimension groups

You'll notice that if you click on a dimension the Designer screen changes, taking you into 'Dimension Mapping Mode'. This is where you can arrange your dimensions into groups (e.g., your customers could be grouped into regions or by sales rep). You can do this now, or go back into your design later. To learn more, see dimension groups and mapping.

To exit 'Dimension Mapping Mode' and return the screen to normal, simply click again on the dimension.

Finalising your design

Once you are happy that the columns have been dragged into the right places, it's time to save and then build your database. You can come back into designer and modify the design, and add more data, even once it has built.