Modify an existing database
The difference between unmapping and deleting
When designing a database, the act of dragging and dropping a column will create both the structure and mapping at the same time. You probably won't even notice this has occurred. However, you are able to delete the structure and unmap separately. The following table illustrates this:
| The blue border means that 'Value' has been mapped from the raw data that appears at the bottom of your screen. Use the to unmap. There are three potential outcomes:
| |
2. Structure | In this situation, 'Value' exists as a structural element only. It isn't mapped, and you won't see it in Phocas after building the database. Often a structure exists as part of a template or a previous design. It often serves as a placeholder, awaiting a future mapping. Use the to permanently delete it. | |
3. Foreign Mapping | The blue dot means that 'Value' is mapped from a different raw data source - not the one that appears at the bottom of your screen. There is no ability to unmap or delete from here - first you'll need to locate the correct raw data file (it could be in another stream, or a different raw data file in the current stream). |
Missing Raw Data
Even if you have the perfect database design, it is possible for raw data files to get moved, deleted, or renamed independently, which could cause a build error.
From Administration > Databases, choose your database and then click Design. If there is a mismatch between the design and raw data, an error like the following will appear.
Clicking OK will take you into Designer, where the missing raw data items will turn red, as shown below. There are three options:
- Restore: Put the missing raw data item back. This could mean re-uploading it, re-Syncing it, and checking the file name.
- Replace: You can easily drag a new raw data file on top of the missing red file. The red will turn blue again, and any existing mappings will be retained. See Replace.
- Remove: If it is no longer needed, you can remove the red raw data file by clicking the X.
Replace
Drag a new raw data file on top of an existing raw data file to replace; existing mappings will be retained. This saves considerable time and effort - to do this manually would require lots of un-mapping and re-mapping of individual dimensions, properties. etc.
- Identically named columns in the new file will remain mapped.
- New columns can appear anywhere in the new file. They'll need to be mapped manually.
- Any removed columns will be automatically unmapped.
- Columns do not have to be in the same order.
Filter
You have the ability to Filter data being loaded into a stream.
To do this, click the filter icon that appears next to each raw data file, as shown.
Next, 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.
Add links
Links allow users to navigate from a Phocas grid into other Phocas databases, or from a database to a dashboard or favorite. They also allow users to navigate from Phocas to another product, passing the current selection to act as a filter. So end-users could, for example:
- click on a customer, and it would open up that customer in their CRM system.
- click on a sales rep, and have it open that particular Rep Scorecard in Phocas Dashboards.
Add a link
To add a link, click the link icon next the the relevant dimension.
The following screen will appear:
Click the green plus icon to add one or more new links, and populate the fields as follows:
- Name - the caption that is shown in Phocas.
- URL - the link code. More details below. Note there is no validation at design time.
Once a link has been added, the link icon turns blue. After creating or changing a link, you need to rebuild the database before the link becomes available.
How a link will appear in Phocas
The 'Code' field is displayed on the user's screen as a hyperlink in blue text, as shown below. If the dimension has a single link, clicking the code value will run the link. If it has two or more, clicking the code shows a dropdown menu with all of the available link labels shown.
Internal link examples
Internal links can be set up using a syntax starting with 'database', 'favourite' or 'dashboard', as outlined below, followed by name (or ID) and the required query.
Syntax | Example | |
---|---|---|
Link to another database | database:Name|query | database:GL|Dimension=Customer&Customer=%% |
Link to a dashboard | dashboard:Name|query | dashboard:Customer Score Card|Customer=%% |
Link to a favorite | favourite:Name|query | favourite:Declining Products|Customer=%%&Mode=Variance |
Note:
- If using ID instead of Name: The ID is an identifier displayed as the last part of the URL.
- Query is the name of the dimension you wish to filter on follow by '=%%'. And, if required, the property relating to the dimension. With a favorite, you might add the mode, etc.
- The syntax you enter must use the UK English spelling, which is favourite with a 'u', even if Phocas displays the word spelt in the usual way everywhere else in the product.