Page Comparison

After you create a database, the next step in the design process is to add (connect) the raw data items (such as files and views) to streams in that database. Even if the Phocas Implementations team designs your databases for you, you might want to add more data items to those databases as time goes on. You can do this in any of your databases, at any time. There are different ways in which to add items to a stream and some methods can save you a lot of time, depending on the circumstances.

Add the transaction file to a new database

The first data item you should add to your new database is the transaction file. This file typically contains data about an activity that has occurred on a particular date, such as sales to customers, orders to suppliers and so on.

1. In the data sources panel, locate the transaction file and drag it into the Drag here box in the New Stream 1 tab.

   

2. (Optional) Click the Preview Rows button to get a preview of the data.

   

3. (Optional) Rename the stream.

4. Proceed to map the data to the database.

Add more data items to a database

It is likely that not all the data required for your database is contained in a single raw data file. In addition to the transactional data, you might want to bring in some more information, particularly about your dimensions. You can add multiple data items, into the same stream or different streams, according to your database design requirements.

Add an item to the same stream

The addition of other data items into an existing stream allows you to populate the dimensions, properties and measures correctly, and it gives more data analysis options to your users.

1. In the Stream section, click the required stream tab to open it.

2. Expand the data sources panel and locate the required item, then drag the item into the Drag here box in the stream tab.

   

   The data displays in a new tab, within the existing stream tab.

   Image RemovedImage Added

3. Proceed to map the data to the database.

Add an item to a new stream

The use of multiple streams adds different layers to your database. The streams are separate; they do not interact with each other. For example, you could add a stream for your current period transactions, one for your previous year transactions and another for your budget. Then, when users open the database, they can switch between those streams to view the different data, or view compare the data within them. Each stream can contain one or more data items.

1. In the Stream section, click the New Stream tab.

2. Expand the data sources panel and locate the required item, then drag the item into the Drag here box in the new stream tab.

   

3. (Optional) Rename the new stream.

4. Proceed to map the new data to the database.

Add a budget file to a new stream

If you want to add a financial budget file as a stream in Financial Statements, see Add a budget file and stream.

Replace an item (use existing mappings)

The Replace option is particularly useful when you want to modifying an existing design. It allows you to replace an existing data item with a new one (such as an updated version), but maintain the existing mappings. This option saves you a lot of time, as you would otherwise have to manually un-map and re-map the individual dimensions, properties and so on.

When you replace an item, the following points apply:

• Columns do not have to be in the same order.

• Identically named columns in the new item will be mapped.

• New columns can appear anywhere in the new item. They need to be mapped manually.

• Any removed columns will be automatically unmapped.

1. In the Stream section, open the applicable stream tab and identify the item (tab) that you want to replace.

2. Expand the data sources panel and locate the required item, then drag the item onto the header of the item tab.

   

3. In the window that displays, click Replace.

   Image RemovedImage Added

Add an item to a stream (use existing mappings - clone and replace)

The Clone and Replace option allows you to apply existing mappings to new items that you add to a stream. The items must have the same structure. This option is particularly useful if your design contains a lot of mappings or transform columns that take a long time to set up.

For example, suppose you have two data items, one for the EU sales and the other for the UK sales but you want the data to display in same stream, where the Total sales value is the sum of the EU and UK sales. You firstly add and map the EU sales data, then add the UK sales data using the Clone and Replace option. The UK sales data will be automatically mapped in the same way.

1. In the Stream section, open the applicable stream tab and identify the item (tab) that has the mapping you want to reuse.

2. Expand the data sources panel and locate the required item, then drag the item onto the header of the item tab.

   

3. In the window that displays, select Clone and Replace. The original item tab is cloned into a new tab that displays on the right, but the data in that new tab is replaced with the data from the new item. The mapping from the original item is applied to the new data, thus creating a new data item with mappings already done.

   Image RemovedImage Added

Clone a stream

The clone option allows you to instantly make a copy of that stream, including its data items, filters and mappings. You can then modify the new (cloned) stream as required.

Cloning a stream saves you a lot of time when designing a new database or adding more data to an existing database. There are many reasons why you might want to clone a stream, such as:

• To enable users to analyze the data by different dates. For example, suppose you have a stream that contains your Sales data. You have mapped all the Dimensions, Properties and Measures but you are left with three date columns (Delivery Date, Invoice Date and Paid Date). You can only map one of those date columns to the Date section in the database. To get around this, you can map the first date column to the Date section, then clone the stream. In the cloned stream, leave everything the same except for the date - change the date column that is mapped to the Date section (map the second date column). Repeat this action to map the third date column to the Date section. As a result, the database has three almost identical streams, the only thing that is different is the date.

• For testing purposes. For example, if you want to try out a different point of view based on the same data. You can clone the stream, make the change and see what it looks like in the database. You can then go back and make more changes or delete the excess streams.

In the Stream tab header, click the Clone button. The new stream tab (clone) displays on the right of the original tab, with a 1 appended to its name. The new tab is automatically open, ready for you to make your changes.

Filter the data in a stream

The filter option allows you to include or exclude specific data in the stream. This is useful when an item contains a lot of data but you are only interested in a subset of that data. The ability to filter data items removes the need to have many individual items - you can simply take what you want from the one item.

Filtering is useful when you want to split one items into two streams. For example:

• Suppose you have a UK company that operates in the UK and EU regions. You add the Sales data item as a stream. That item contains data for both regions but you are only interested in the UK sales data, so you filter out the EU sales data.

• Suppose you have a financial database in which Stream 1 has a General Ledger file with Balance Sheet and Profit and Loss transactions. You can clone that stream (see section above), then filter Stream 1 to display only the Profit and Loss transactions, and filter Stream 2 to display only the Balance Sheet transactions.

1. In the stream section, in the data item tab, click the Filter button.

   Image RemovedImage Added

2. Click the green plus button to add a filter, then configure the filter as required.

3. Repeat the above step to add more filters.

Example : - Filter sales transactions

The following filter configuration will filter the sales transactions; only transactions with a value greater than $10 will be included and any transactions involving the customer USPA_136-004 will be removed.

Example - Filter Balance Sheet and Profit and Loss transactions

The following filter configurations will filter the General Ledger transactions in two streams; only the Profit and Loss transactions will display in one stream and only the Balance Sheet transactions will display int the other stream.