Page tree
Skip to end of metadata
Go to start of metadata

This article is an introduction to the basics of data sets. It is most useful for new users that are learning the Advanced Analytics Module. For the full documentation, please visit http://support.belladati.com/doc/Data+Sets. We also have two webinars available through our e3 Program.

Data sets are the foundation upon which everything else in ProjectorBI is built. Data sets are used to populate dashboards, populate reports, used in formulas, and for joining data together. You need to understand data sets to understand ProjectorBI as a whole.

Let's start at the simplest level. What is a data set? It is most analogous to something we are all familiar with - a spreadsheet. Imagine a spreadsheet with columns Resource, Cost Center, and Hourly Rate. Then you add rows of data to it. A data set is just like this.

You can populate your data sets by entering in data row by row, just like in a spreadsheet, but where data sets really get there power is from data sources. A data source is another system that holds data. That system could be Projector, Salesforce, Google Analytics, your own SQL database, etc.. Your ProjectorBI instance comes with easy ways to connect to data sources. We call them Connectors. By default, your installation has only one connector, for Projector. You can enable up to two additional connectors. A full list of connectors is available here - http://support.belladati.com/doc/Data+Sources. If you need more connectors, please contact Projector support for assistance.

For instructions on using a specific connector, see:

Permissions and Settings

The table below outlines who has access to the Data sets menu item in ProjectorBI and who can view a data set.

User RoleDescription
Data Set RoleThis is the important one. You cannot make changes or do anything with a data set unless you have this role. Can create their own data sets. Can edit data sets that are shared to them.
Domain Administrator RoleCan view and modify all data sets, even if not owned or shared to the admin. Typically only one admin per system. Do not use this role to give data set access. Use data set manager instead.
Report Editor RoleGrants no abilities to view or edit data sets. However, they do gain access to the data sets menu item.
No User RoleCan only view data sets that are shared to them. They have no access to the data sets menu item, but can still get to them by drilling in through a report or by direct link.


In addition to the high level permissions above, there are fine-grained permissions that limit access to specific columns of data or views that show a column of data.

ActionDescription
Visibility

Hide an attribute or indicator from your report editors. For instance, hide an informational column that shouldn't be used in reports. Or hide a column that isn't ready for report editors to use yet. If you hide something that is already in use in a report, the report continues to show it. But future reports are blocked.

  • Keep report editors from using this item in reports
  • Prevent users from browsing this column's data
  • Hide from formula editor
LockLock down who can access a view that contains this attribute or indicator. When someone without access tries to look at a view that uses one of these indicators, they are shown the message "You don't have permission to see this view." For instance, block all views containing RDC to everyone except administrators. This is a positive filter. Which is to say, that anyone on the list can see it. Anyone not on the list, cannot.
FilterApplicable to attributes only. Filter a data set to match the logged in user. For example, you have a view that shows salary for everyone in your organization. When Jim looks at the view, he only sees his own salary, not everyone else's.


Data Sets Manager

The data set manager is reached from Topics | Advanced Analytics | Data set menu item.

 Browse your personal data sets and data sets shared to you. You can only edit data sets that you own or are shared to you with edit capabilities. 


You can review who has access to data sets by ticking the Show permission details checkbox. Columns appear that show you which individual users and groups have view or edit permissions.

In the left column you'll find the following options.

ActionDescription
Create data setMake a new, blank data set. After creation you'll have to go in and add data to it.
Upload dataMake a new data set and immediately start populating it with data from xml, csv, Excel, or just cut/paste it in.
Upload from data sourceUse a prebuilt connector to access data in other systems like Projector, Salesforce, LinkedIn, or even your own databases.
Map charts GeoDataGeodata maps let you combine point locations, like cities - or region outlines, like country borders, with information in another data set. These create powerful geomap visualizations. Your installation includes a few GeoMaps by default. You can add your own if you wish. Please contact Projector support if you need assistance.
Data changes monitoringIf you have specified an email alert on your data sets, then you can review all of your own alerts here.
Transformation scriptsIf your organization has special scripts that they run on imported data to format or alter it, then you can review those special scripts here.
Database connections libraryIf your organization connects to custom SQL databases accessed over the web, then this library describes all of those connections.



Data Set Details

So far you have been introduced to the major ideas around data sets. Permissions, management, sharing. This section details actual features.

Data Set Summary

The summary page shows you past imports, the status of those imports (success, failure) and details about what was imported. It also lists all the reports tied to this data set. From this page you can:

  • Edit data set name and description
  • Schedule imports
  • View all past imports
  • View reason a data set import failed
  • Visit any report based on this data set


Attributes

Attributes are columns in a data set that describe data. For example, imagine Tim in New York earned you five thousand dollars. Resource and Location are attributes. They describe the five thousand dollars. Dates are also considered attributes. The corollary to attributes are indicators. Indicators are discussed a couple sections further down.


Actions

IconNameDescription

Add new attributeCreate a new attribute. If this data set is part of joined data sets, then this is the only way you should add attributes. You can also add attributes through import templates, but if you use that method it won't update the joined tables.

NameThe name of the attribute. duh.

CodeThis code is used to access the attribute for use in formulas. For example, value('L_ACTIVITY_TEXT'). Attributes always start with "L_" and are capitalized. It is also used when creating parameters for project workspaces.

ETL column name

ETL is short for extract, transform, load, three database functions that are combined into one tool to pull data out of one database and place it into another database.

The ETL name is used to migrate data from one database to another. When you import data from external systems, those systems likely have their own column names different than the one you use in ProjectorBI. For example, Projector reports use Resource Display Name and ProjectorBI uses Resource for brevity. ETL name is the bridge between your data source and your data set columns. Your ETL names and data source column names should match. In this way, you can change the names of your attributes and indicators, without breaking your import.

Custom Member

Custom members are assigned to an attribute. You specify what values contribute to that grouping. Finally, when drilling down on that value in a chart or table, the custom member name will be shown. This is complex, so I'll do my best to give a real world example.

Let's say you have an attribute called Resource Title. You want to drill down on Resource Title in a chart, so you do that. You end up with something that looks like this:


Now what if you wanted to group two of those categories together? Let's group Jr. Technical Consultant and Sr. Technical Consultant into one bar called 'Technical Consultants.'

  1. Add a new custom member to 'Resource Title'
  2. Set the custom member conditions to include Title = Jr. Tech Cons and Title = Sr. Tech Cons
  3. Rerender your chart, now it has a new category with this custom grouping


Custom members are not only limited to the drill-down member's values. You can drive a custom member by other attribute values too. For example, my 'Technical Consultants' group may be composed of specific engagements, or a combination of engagements and titles.

Custom members are also useful for forcing disparate data into a drill down. For example, I have a table that shows revenue by cost center, but I want to also show revenue by contract type at the same level. I’d create three custom members at the cost center level. One for T&M, one for NTE, and one for FP.  

The result is my table shows revenue by cost center by default, with two special rows that show revenue by contract type.  It might look like: 

East:  50k
West: 125k
North: 25k
South: 75k
T&M/NTE: 200k
FP: 75k


SubsetCreate a group of like attribute values. For example, you have many expense types. Airfare, Taxi, Hotel, Licensing, Entertainment, etc. You create a subset of Airfare,Taxi, and Hotel and call them "Travel." Now your report editors can easily create visualizations for the subset in one click.
Appearance and Translation

This button really does two things. First, it lets you set the default colors for attributes. For example, you have a low, medium, high priority list. Set them to green, yellow, red colors by default. The colors are then reflected in reports that include them. Colors can be overridden on a per report basis as needed.

Second, you can translate the name of the attribute for different languages. For example, Spanish users would see "pasaje aéreo" instead of "airfare."

Visibility

Hide this attribute from the user interface. When a user browses a data set, this column will be hidden. When a report editor creates a report, this attribute won't be shown in selection lists. Nor will it be available in formulas. However, any existing report that uses it already will continue to use it.

You might use this if you have an old report column that should no longer be used. So you hide it from view.

Transform Attribute Values

Use a formula to transform all existing values in the data set to new values. For example, if a name field was formatted Last Name, First Name - you could switch the two.

This is a one time transformation. If you want to automatically transform values on all incoming data, you'll need to do so via the import template instead.

Restrict Access to UsersAny view that uses this attribute is not rendered. Instead users are shown "You don't have permission to see this view." By default, attributes are viewable by anyone. Once lock is enabled, that logic is flipped. It is available to nobody except for people on the view list. Useful when you have information that only a select group of people should view. For example, Resource Pay Category is restricted to management.
FilterWhen displaying a view, filter the data set to match the logged in user. For example, you have a data set which contains salary for each user. With a filter, the logged in user can only see their own salary.
DeleteDelete the attribute.



Drill Down Paths

Drill down paths are a very cool feature. When building reports, you'll often need to provide users a way of drilling into the details. For example, a report shows revenue by engagement. Users drill down into an individual engagement to see it broken down by project. Here at the dataset level you create a tree view of these drill downs for use later on. In the screenshot below you can see that I have a single parent node which then branches off to two children. When report creators add a view, they'll be presented with this branching structure. By clicking on one of the nodes, the view automatically builds the drilling path for them.


Example screenshot of picking a drill down path during view creation. The light blue nodes are the path that will be used.



Indicators

Indicators are numbers. For example, hours, revenue, or contract amount. Attributes describe those numbers. For example, the project revenue sits on. Because 


Actions

IconNameDescription

Create Indicator

Create a new indicator. If this data set is part of joined data sets, then this is the only way you should add indicators. You can also add them through import templates , but if you use that method it won't update the joined tables.

When you create an indicator, you can optionally specify a formula. Logically, you might assume this adds a new column of data. For example, you have existing indicators Rate and Hours. You make add a formula indicator for Revenue = M_RATE * M_HOURS. Now I would have a new column with Revenue values in it. But that is not how this works. Rather, it is just a shortcut for creating an indicator formula for report editors. It shows aggregated Rate * Hours and never shows individual row data. If you need row level data, then use an Import Template instead. This creates a real column in the data set. If you still want to use a formula, see ForEachRow instead.


Create a group of indicatorsYou can create groups of indicators for ease of adding them to views. For example, group system rate, contract rate, and standard rate together. Then all three are added to a report at once. Some views also require indicator groups for display purposes. You don't have to create your groups here. You can create them on a per dashlet basis if you want.

NameThe name of this indicators as shown to report editors

Code/FormulaThis code is used to access the indicator for use in formulas . For example, M_Hours * M_Rate = Revenue. Indicators always start with "M_" and are capitalized.

ETL Column NameWhen you import data from external systems, those systems likely have their own column names different than the one you use in ProjectorBI. For example, Projector reports have "Resource Display Name." But in ProjectorBI you might just call your attribute "Resource." ETL name is the bridge between an ProjectorBI column and data source columns. That way when your attributes/indicators change names, or your source data columns change order, the import template can still figure out how to map between them. For example, you have source columns A, B, C. They map to attributes Time, Hours, Revenue. If you change the column order to C, B, A - ProjectorBI won't be fooled. It will check the ETL name and ensure the data goes to the correct attribute/indicator.
TranslationTranslate the name of the indicator for different languages. For example, Spanish users would see "dolares" instead of "dollars."
Visibility

Hide this indicator from the user interface. When a user browses a data set, this column will be hidden. When a report editor creates a report, this attribute won't be shown in selection lists. Nor will it be available in formulas. However, any existing report that uses it already will continue to use it.

You might use this if you have an old report column that should no longer be used. So you hide it from view.

Transformation

Use a formula to transform all existing values in the data set to new values. For example, if data came in featuring a lot of trailing decimals (1.0023940390403), you could round all the values to two decimal places.

This is a one time transformation. If you want to automatically transform values on all incoming data, you'll need to do so via the import template instead.

LockAny view that uses this indicator is not rendered. Instead users are shown "You don't have permission to see this view." By default, indicators are viewable by anyone. Once lock is enabled, that logic is flipped. It is available to nobody except for people on the view list. Useful when you have information that only a select group of people should view. For example, RDC information is only available to management.
DeleteDelete the indicator


Joined Data Sets

Joined data sets are a special kind of data set. They are formed by taking two or more other data sets and combining the information. Once the data is combined, every attribute and indicator in the source data sets are available in the joined data set. This is extremely useful for expanding the scope of two disparate reports. For example, you example you have one report that contains all your resource information (location, skills, etc). You then join it with a time card report. The joined report knows not only time cards, but the resource location and skills that contributed to that time card. 

ProjectorBI supports the following types of joins.

  • Left Outer Join
  • Inner Join
  • Cross Join
  • Custom Join Condition

This article doesn't get into explaining all the different types of joins and why you would use them. You can research SQL joins online or work with your a Projector consultant it you need assistance building data sets that meet your business needs.

It is good practice to identify your joined data sets by prepending their names with "JD-." Your installation includes two by default, JD-ProjectPortfolioPerformance and JD-GinsuByWeekWithProjectDetails.

Editing Joins

If you delete a join, the non-primary tables attributes and indicators are removed from the data set. They will also be removed from any dependent reports. However, if you add the join back in, the reports should self-heal. This is a useful behavior when you accidentally forget to populate an attribute/indicator out to joined tables. You can simply delete and re-add the join to fix. The following are rules for editing joins.

  1. Left joins and inner joins can be switched between one another. They can also be turned into a cross join.
  2. Once you have a cross join, you can't switch the join type. You can only delete the join and recreate it.
  3. Similar to cross joins, custom join conditions cannot be edited. You must delete the join and recreate it.
  4. There is no concept of right join. You'll need to change the primary join table to the other data set if you need a right join.

Automatic Rebuild

The automatic rebuild feature allows you to control when a joined data set is updated. If one of the source data sets for a join updates with new information, that new information must be populated out to the joined table. With automatic rebuild off the table always updates immediately. This might sound like the best choice, but updates to large tables can be time consuming. For this reason we recommend that automatic update be turned off only for tables under 100k rows. 

If you do have a large table, then you'll want automatic rebuild on. With it on BellaDati executes the rebuild during the next available materialization window. If you manually updated the source data set, you'll be prompted to rebuild immediately. Please contact Projector support if you need to learn more about your materialization windows.

Best Practices

Projector recommends the following practices when using data sets.

PracticeDescription
Use AAM- and JD- naming conventionsWhen naming your Projector reports or AAM Data Sets, prepend them with AAM- or JD-. This allows you to quickly identify the source and type of data sets. For other import types like Salesforce you might want to create your own naming convention, like SF-.
Schedule during down timeTry and import data during off hours. This helps keep the system running efficiently when day-to-day users are logged in,
Leverage Joined Data SetsCut down on data usage by leveraging joined data sets.
Limit report size

There are several ways to limit the scope of data imported.

  1. Only pull data for date ranges that make sense. Don't just pull data for all of time.
  2. Only pull fields relevant to what reports require.
Table of Contents
  • No labels