Analyzing Data Using Pivot Tables

You can analyze data using a pivot tableA pivot table is an interactive table that allows you to summarize data—to count, total, rank or average data—in a result set, by dragging and dropping results columns to different rows, columns, or data positions. You can arrange pivot tables dynamically; for example, if you are working with a pivot table summarizing trade data, you may be able to easily rearrange the data elements used in the pivot table so that it summarizes the data based on rep or date groupings or on trade class. This process of rearranging the table is known as pivoting your data; in other words, you're turning the same information around to examine it from different angles., starting from a number of data sets in SalesConnect.

1. Select the data that you want to analyze.

   • Data for one or more connectionsConnections are entities; they're the different types of companies and individuals you may need to track. SalesConnect is set up to display information about the types of connections you care about, based on the roles to which you are assigned. such as repsA rep is perhaps the most important contact connection in the intermediary business hierarchy. Each rep is an individual who sells funds and is affiliated with an office (and through the office, with a broker/dealer firm). Reps may also be referred to as "financial advisors." Possibly the most critical information you can track for reps are aliases, also known as trading IDs. Aliases are the IDs that associate transactions and assets with each rep. If you need to write queries or reports: The primary data for reps is stored in the Contact and Rep Profile tables. The Rep Alias table stores trading IDs associated with reps., rep partnershipsA partnership is a contact connection in the intermediary business hierarchy, though it is really a name for a group of reps working together to sell one or more products. A partnership, sometimes called a rep partnership, is treated as a special type of rep: this means that most of the tools for working with reps may be used to work with partnerships as well. Most views of transactions associated with an individual rep generally don't include transactions or parts of transactions that the rep may have cleared as part of a partnership. Instead, these kinds of trades are listed only for the rep partnership. Each member's portion of rep partnership sales data is always based on the percentages currently assigned to each member; no long-term historical information about percentages is maintained. SalesConnect does not store any calculated trade or asset values based on rep partnerships. If you need to write queries or reports: The primary data for partnerships is stored in the Contact, Rep Partnership, and Rep Profile tables. The Rep Alias table stores trading IDs associated with partnerships as well as reps., teamsA team is a contact connection, though it is really a group of individuals working together to achieve a common sales goal; a team may include reps and rep partnerships from the office with which the team is associated, but it can also office contacts and non-producing reps who support team sales efforts., officesAn office is a connection in the intermediary business hierarchy, an organization subordinate to a broker/dealer firm. Offices are also sometimes referred to as "branches'; institutional firms have branches in SalesConnect. An office is affiliated with a single broker/dealer firm, and may have one or more reps directly affiliated with it. An office may have associated office contacts, as well. Possibly the most critical information you can track for offices are aliases, also known as trading IDs. Aliases are the IDs that associate transactions with each office. If you need to write queries or reports: The primary data for offices is stored in the Office table. Information about office trading IDs is stored in the Office Alias table., firms or master firmsA master firm is a connection associated with intermediary business, an optional top-level organization in the intermediary asset management hierarchy, one step above broker/dealer firms. Each master firm may have one or more firms affiliated with it, and through these firms, may also have affiliations with offices and reps. A master firm may have associated master firm contacts, as well. If you need to write queries or reports: The primary data for master firms is stored in the Master Firm table.. On the detail page for a connection, you can select , and then Analyze. Or, you can analyze several connections in a list: In a table listing connections (such as search results or a tab on a detail page), if you want to limit those included, select checkboxes for those you want. If you want to include all listed connections, don't select any checkboxes. Select Analyze in the Select a Group Operation dropdown list above the table.

   • The results set for any saved analytics queryAn analytics or BA (for business analytics) query is a special type of saved search that selects data from SalesConnect so that you can work with it in pivot tables. When you create an analytics query, you select query criteria based on objects and columns/field names, just as with other queries. But analytics queries are different from standard queries, which are saved searches for key connections like firms, offices and reps; analytics queries are more open-ended.: Using the Administration personaA persona is an interface set of menus and dashboard or home view that was designed for a single set of operators who share goals, skills, attitude and behavior patterns. Each persona's interface displays only the content and tools that are relevant for that group., select Analytics from the Feature Configuration menu. Then, on the Analytics QueriesA query is a special type of saved search for connections or other data in SalesConnect, created with a SalesConnect query tool rather than a standard search page. When you create a query, you select query criteria based on familiar field names from the interface; the criteria you specify are transformed behind the scenes into the appropriate SQL statements. page, select the name link for the query you want. On the results page, select checkboxes for those you want to analyze, and then select Analyze in the dropdown group operationsA group operation is a task that you can schedule or complete for several connections or other records by first selecting the group and then selecting the task. Unlabeled group operation dropdown fields appear over many tables in SalesConnect; to use them, first select the checkboxes for the listed records you want, and then select an operation from the list. list above the table.

     Checkboxes are shown only for queries that use specific objects as their root object; these are generally the primary objects for connections (so, objects like Contact, Office, Firm). If you want to be able to use checkboxes with a particular query, consider rewriting it to use the primary object for a connection as its root object. Keep in mind that even when checkboxes are not shown for a query, you may still be able to analyze its results, but that analysis will include ALL results.

2. If no pivot view has been set and saved for the data set, a Pivot Setup popup is shown. You can use it to create a pivot view to use. If there is a default view for the data set, the pivot table page is shown. You may be able to display more details, drill down on data, or change sorting in the pivot table by selecting caret buttons:

   Choose what you would like or need to do next from these options.

   Create a view for the pivot table

   Whenever you load a saved cubeIn analytics, a cube is a data set that combines several dimensions on which you can basis your analysis of data. A dimension is a set of related attributes; for instance, some of the key attributes in a transaction dimension would include: transaction gross amount; transaction posting day, month and year; and transaction type. Each dimension relates to one or more groups of measures in the cube; each measure represents a column that contains quantifiable data, usually numeric, that can be aggregated. Attributes may represent all of the data in a column or part of it; for instance, a single date column can represent multiple attributes: the transaction posting date column yields transaction posting day, month and year attributes. that has no saved view, the Pivot Setup popup is shown. Or you can select on a pivot table to display it.

   1. In the Properties list, select a propertyIn SalesConnect, a property is an attribute for one aspect of the state of an object. For instance, the contact object has properties that include a first name (firstName) and last name (lastName). (You can think of a property as roughly equivalent to a column.) you want to include in the view, and drag it to the Rows, Columns or Data list to begin setting how you want to summarize data in the active cube. In the Data list, you can select the aggregate function you would like to use for each data element. Repeat as needed.

      The Properties list shows properties included in the active cube, listed under the objectsIn object-oriented programming, an object is a software bundle of related attributes (properties) and behavior (methods). Software objects are often used to model the real-world objects that you find in everyday life. Often, you can think of an object as roughly equivalent to a table in a database, though the object has additional logic attached to it. For example, one type of "object" in the real world and in a SalesConnect application is a person, known in SalesConnect as a contact or connection. Each contact has many properties, such as a name and birth date; in SalesConnect, these are properties of the object Contact. In the real world, each contact also has many behaviors; in software such as SalesConnect, you are typically interested only in recording or managing certain types of behavior for Contact instances. Objects in SalesConnect are logically named entities mapped to Java classes: objects are the instantiations of classes. So, the class for the Contact object includes methods for setting and retrieving key properties for Contact entities, such as the contact's name. An object may call its own methods, or it may call another object's methods that are visible to it. In SalesConnect, the properties for objects defined for an application project are most typically mapped to specific database table columns. to which they belong. A property may already be included in the Data list, if it is the default aggregated value for the active cube. Keep in mind that the data included in the cube determines and limits the data that you can analyze.

      If you are working with a pivot table for a BA queryAn analytics or BA (for business analytics) query is a special type of saved search that selects data from SalesConnect so that you can work with it in pivot tables. When you create an analytics query, you select query criteria based on objects and columns/field names, just as with other queries. But analytics queries are different from standard queries, which are saved searches for key connections like firms, offices and reps; analytics queries are more open-ended., you may be able to change that query to change the data set with which you are working. If you are working with other analysis tools, you may be able to select a different cube to work with.

   2. Select OK in the dialog when you have finished. A pivot table page for the cube is shown.
   Save a new or updated view for the pivot table

   1. On a pivot table page for a cube, select . A Create or Update Views popup is shown; its name includes the name of the data set with which you are working. Use the fields in the Update Current View section if you want to update the definition for the active view, or the ones in the Create View section if you want to create a new view based on the current settings.

   2. If appropriate, change the name by entering a new one in the appropriate View Name field.
   3. If you want to make this view available to all operators, select Public View.
   4. If you want to make this the default view for the active cube, selectDefault View.

   5. Update or create the view:

      • If you have been updating the definition for the active view, select Update in the Update Current View section of the dialog. The dialog closes, and your changes are saved.
      • If you have been defining a new view you want to save, select Create in the Update Current View section of the dialog. The dialog closes, and the new view is created.
   Load a saved cube and view for the pivot table

   1. On the pivot table page, select .
   2. On the Load Cubes and Views popup, select a cube or view, and then select OK. The selected cube or view is loaded to the pivot table.

   You can delete saved cubes and views: Select the cube or view on the Load Cubes and Views dialog, and then select Delete View.

   Display the pivot table's data as a chart

   You may be able to display the pivot table's data as a bar or pie chart.

   1. On the pivot table page: Select to display the data as a bar chart, OR select to display the data as a pie chart.
   2. If the chart has not already been set up for this data, the Chart Setup popup is displayed. Select one item in each column at the top to select options for the chart, select options for Sorting and Top/Other as appropriate, and then select OK.

   If you use a browser that doesn't support the HTML5 canvas element (like Microsoft Internet Explorer 8 or earlier), you won't be able to view charts.

   Display the pivot table's data in a table

   Once you have displayed the pivot table as a chart, you can easily view it again as a table. On the pivot table page, select .

   Export the pivot table's data set as a comma-separated values (CSV) file

   You may be able to export the pivot table's data set as a comma-separated values (CSV) file, depending on the browser you are using, and the features available in it. CSV files may be opened in Microsoft Excel or other spreadsheet tools, where you may be able to use the data analysis options available in those applications to work with this data.

   1. If available on the Business Analytics Portal page, select . The Pivot Export popup is shown.
   2. Select Save Current Slice to Disk (.csv) or Save All Data to Disk (.csv). Depending on your browser and settings, you may be offered options to open or save the file, or the file may automatically open or be saved to your computer. If necessary, you can save the file and then open it in the application you would like to use to work with this data.
   3. Select when you have finished with this export.