Data Sources

From DataSelf Knowledge Base
Jump to navigation Jump to search

Managing DSA Data Sources

Publishing DataSelf Web Server/Tableau Data Sources

DataSelf provides preconfigured data source templates for a variety of Reporting Packages. The publishing process makes the templates, and their data, available for reporting. DataSelf's guide for how to Publish Data Source templates to DataSelf Web Server/Tableau Web Server.

Links:

Underlying Data Sources

DataSelf's OEM version of Tableau allows two data sources:

  1. DataSelf Microsoft SQL data warehouse databases
  2. DataSelf Analysis Services cubes.

DSA Desktop can connect directly to

  1. DataSelf Microsoft SQL data warehouse databases
  2. DataSelf Analysis Services cubes
  3. data sources published to DSA Server
  4. stand-alone DSA Extracts created from a DataSelf SQL data warehouse database (DSA Desktop can both create and refresh Extracts.)

Workbook views and dashboards can be published to DSA Server using any of the above data sources.

DSA Server data sources can be

  1. extracts of DataSelf SQL data warehouse databases, published to Server (the most common option)
  2. pass-throughs to DataSelf SQL data warehouse databases
  3. pass-throughs to Extracts stored outside DSA Server
  4. pass-throughs to Analysis Services cubes

(Analysis Services cubes are already optimized for fast reporting performance, so they are never turned into Extracts.)

DSA Standard Data Source Configurations

DataSelf Analytics systems' standard configuration is to publish data sources to DSA Server for users to use as the basis of their reports. These data sources are usually Extracts (from a DataSelf SQL data warehouse). A DSA Server on the same network as the SQL data warehouse can be scheduled to automatically maintain and refresh the extracts within itself. A DSA Server in the Cloud will need to have fresh extracts delivered to it on a scheduled basis.

Workbooks can then be created based on the DSA Server datasources. Within the workbook, additional formulas, calculated members, sets etc can be created for use within that workbook. These created objects can be copy/pasted from one workbook to another, and even whole workbook tabs can be pasted into a different workbook. Therefore, the report writers have a wide latitude to enhance their reporting, even if the underlying extract is not changed.

As additional fields are required for reporting, they can be added to the extract and re-published to DSA Server. As long as fields, calculations, and other objects currently in use by any workbooks are not removed from the Extract, this modification to the DSA Server Extract data source will not negatively affect the existing workbooks which are based on this data source.

DSA data can also pull from a direct connection to the SQL data warehouse. Simple visualizations and dashboards from a table up to a million or so records can have satisfactory performance if attention is paid to constructing the visualizations to optomize performance. More complex visualizations can still have satisfactory performance for up to about 500,000 records. A rule of thumb is: how quickly would SQL itself be able to return this data with these groupings, filters, and formulas?

SQL vs Cubes as data source

Usually, we prefer to use SQL as the data source, since it allows the end user (or a moderately competent SQL view creator) the most number of options of creating their own formulas and groupings.

Understanding Functional Differences Between OLAP and Relational Data Source Connections

http://kb.tableausoftware.com/articles/knowledgebase/functional-differences-olap-relational

When to use a Cube instead of a SQL data source

  1. parent-child dimension (Financial reports, and use Excel to control precise formatting and easily add other metrics such as ratios) (You may wish to present some GL trends and ratios graphically within DSA, which do not require the special formatting that “real” Financials do.)
  2. Certain multi-fact-table reporting where the blend source needs to include dimension members not included in the main data source. Example:Inventory Projections (Which combines records from Inventory OH, Open SO, Open PO, Sales History, and sometimes additional Sales Projections)
  3. Many-to-many reporting

Differences in reporting when using a Cube data source

Calculated Member

Reporting from OLAP cubes does not allow the “Group” function. You can use Calculated Member instead. (Correction: Access from the Dimension panel menu in upper-right corner)
https://dataselfsupport.com/attachments/92/DSA_CalculatedMember.pdf