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.
| Tip |
|---|
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
| Info |
|---|
This page outlines how to create a brand new database from scratch. Alternately, you can clone an existing database and modify its design to meet your needs. |
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?
Generally, there will already be data there? i.e. file sin the list that you would add as a stream. so this step is not really required at this stage? more of a separate topic?
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).
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 ). doesn’t work for me
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.
| Info |
|---|
If you want to upload a budget stream file into the database, depending on the format of the file, you might want to take advantage of Designer's ability to automatically detect a budget file and convert it accordingly. This is sometimes referred to as Easy Budget Upload method. |
Select/use a sync file
Select a file how?? that has been uploaded via Sync. See Using Sync.
Configure the database
After creating a blank new database and uploading or connecting to a data source, you need to bring that together by configuring the database. Firstly, you add data files to 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 data to a stream
It is possible that not all required data is contained in a single raw data file. You can populate a single stream with multiple raw data files.
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.
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.
Clone a stream
why would you do this here? duplicate data?
To save time when designing a database, you can clone a stream using the clone icon next to the stream name, as shown in the image below. This clones the stream's data items, filters and mappings. In the screenshot below, the user has cloned 'Orders'. A copy appears to the right of the original, named 'Orders (1)' and will automatically become the selected stream.
Check the date format
When uploading via a flat file or CSV file you need to check the format in Designer to ensure it matches the format in the budget you have uploaded.
is this the same as a static file? is this only applicable for budget files?
Note the format in the 'moment' column. In the example below the format has come into Designer as date/month/year i.e., dd/mm/yyyy (01/03/2020).
Go to 'Date' in the top right portion of Designer and select edit.
Select the required date format. In this case, it is dd/mm/yyyy.
Map the stream components / raw data to the database components
The raw data that you dragged will appear in the box under 'New Stream'. Each column of unmapped data is marked by a red dot. Mapped data columns show a blue dot.
This step influences what your users see in Phocas and how they are able to conduct analysis, so it is worth spending some time thinking about what structure will make sense for your business. You can map some data, build a database and try it out in Phocas. If it's not what you want, simply edit your design. It's quite common for even experienced administrators to revise their design to get it just right. Also see Modify a database.
Manually map items
To map, drag and drop a column heading into the appropriate 'list' (Dimension, Property, Measure or Date) in the top part of the screen, as shown below. In this screenshot the user is adding 'Quantity' to the list of measures. Note that a property can't have the same name as a measure.
If you make a mistake you can remove the mapping using the 'X'. You can rename the item and change other details using edit .
| Note |
|---|
Note about streams and mapping: While the 'X' here only removes the mapping for the relevant, deleting an entire stream (in the lower part of the screen) causes you to lose whatever mapping you have done, so take care when using delete. |
Once the item is mapped, the red dot turns blue, with an initial to show what it has been mapped to, eg., 'D' to indicate mapping to a dimension, etc, and the name.
For example, in the image below the column titled 'LinkField' (the name in the raw data file) has been mapped to the 'Ship to' dimension. If no date is mapped, Designer will default to today's date. If no measures are mapped, designer creates a single 'value' measure (with a value of '1').
Use existing mappings for a new data item (stream or file?)
To save time you can applying existing stream and dimension mappings to new data that you drag into Designer using a 'clone and replace' option. This is particularly useful if your design contains a lot of mappings or transform columns.
To to this, identify the data item that has the mapping you want to reuse and then drag the new item in from where? data sources file? on top (like you do when replacing a stream). You'll get an option to either replace the item or to 'clone' the original, keeping its mapping but applying it to the new data, thus creating a new data item with mappings already done.
Hover on a stream to see the data source.
Map data from multiple files
It is possible that not all required data is contained in a single raw data file. You can populate a single stream with multiple raw data files. Drag additional raw data files, as shown below. The new file will now appear next to the original one, ready to be mapped.
Usually, the data from the second and subsequent file(s) will need to match the structure already created from the first file. Therefore, drag columns and dock them on top of the corresponding mappings. The 'x' will turn blue to show that they have been successfully docked. In this example, the quantity column from the 2014 raw data file is being dragged on top of the quantity measure that was previously mapped. why not use existing mappings method above?
If you drop a column onto the wrong measure, you can click the 'x' to remove the mapping. The item that you dropped should now be available again for further docking.
Change the order of properties relevant only for specific charts?!!
The order in which properties are mapped, and their naming convention, will affect the results that users see when using the Map charts (Marker, Circle and Heat) feature in Phocas.
Drag and drop to change the order of properties, and select edit to change a property name.
Notes about naming:
Country 'AUS' does not map. Use 'AU' or 'Australia'.
Use 'Due date' not 'Date due'.
How to test a property
To test mappable properties enter the string into https://www.openstreetmap.org.
Map dimension groups
After transaction file mapping is complete you can start mapping dimension groups, if required. A group is just a way of arranging your dimensions. For example, your customers might be grouped into regions, or types, or even by which sales rep looks after them. Products might be grouped by category and sub category. A group can be anything that works for your business, as long as the data exists.
To start, click on an already mapped dimension. This takes you into 'Dimension Mapping Mode'.
In the usual manner, drag across the relevant raw data file from the expandable panel on the right. Here we've dragged the 'Product 222' raw data file.
Mapping is done in the same way as described above, with two exceptions:
You'll see a data box to the left of the raw data with a red heading. This represents the data you've already mapped, and you'll need to link your raw data to it. Just locate the column that has the matching data, and drag it across as shown. The column heading will turn green.
To create a dimension group, drag the raw data column on top of the appropriate dimension. In this example, we're in the process of dragging the raw data column 'Minor Section' on top of the Product Dimension. It will then appear below, grouped.
To exit dimension mapping mode :
Click on the dimension again. It works as a toggle.
Click on any blank space in the dimension box.
Note: If no dimensions are selected, you'll see the transaction mapping.
Include a group as a dimension property
After mapping a dimension group, click edit and select one of the 'include as property' options to set this as property of the parent dimension.
The newly created property will be named as per the title. Note that properties added this way will not appear in the regular Properties box in Designer.
Change the order of dimension data files
You can drag data files to change their order within a dimension.
To do this select the relevant dimension, as shown below. This will show the data files from left to right across the screen. Drag to change their position. The order of these files (from left to right) determines the order in which they are processed during a buid.
Hide code in matrix column headings
You can set matrix mode column headings (known as 'captions' in the options menu on the grid) to default to name only, by ticking 'hide code'. With this ticked, the user sees only the name for the selected dimension and does not have the option of changing it, e.g., to 'name and code' or just 'code', as the matrix captions option is disabled.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.
If you are creating a financial database, select the Financial Database checkbox*. Please read the Design a finance database for important information.
Click Save.
The Design screen opens on the Design tab.
| Info |
|---|
*This option is only available if you have the Financial Statements license. |
Related pages
| Panel | ||
|---|---|---|
| ||
Next step in the design a database process: |