Skip Ribbon Commands
Skip to main content
SharePoint
Last modified at 9/17/2015 9:06 AM by Peter Scordamaglia

​​[back to Pulse BI Wiki]

 

Technical Details


This page presents the technical details of the Pulse BI implementation.  Following sections are available:

  1. Architecture.  This section describes the architecture of the Pulse BI implementation.
  2. File System.  This section describes the file system arrangement.
  3. PulseBI Program.  This section describes the PulseBI program.
  4. Internal Functions.  This section describes the internal functions used by the Pulse BI implementation.
  5. Data Warehouse.  This section describes the data warehouse structure for the Pulse BI implementation.
  6. Data Marts.  This section describes the data marts in the Pulse BI implementation.
  7. Dashboards.  This section describes the PowerPoint dashboards in the Pulse BI implementation.

 

Architecture


This section describes the architecture of the Pulse BI implementation.  The architecture of the Pulse BI implementation consists of several components as presented in the table below:

 

Componen​t​ ​Description
​File System ​This is the file system on a Windows server that contains the Pulse BI program, configuration files, schema files, log files etc.  Also, input files to be loaded into the Data Warehouse are also placed into this file system.
​PulseBI Program ​This program is responsible for loading the data warehouse configuration, generating schema scripts, and refreshing data marts as specified.
Configuration ​Pulse BI is a configurable system.  The configuration of the system defines data marts, facts/dimensions, attributes/measures, and other aspects of the data warehouse and data marts.
​Input Files ​Input Files.  Input files are CSV files that contain data that needs to be loaded into Data Warehouse.  The configuration describes what input files will be loaded, how the data warehouse will be updated from based on the input files etc.
​Data Warehouse ​Data warehouse serves as a storage of data.  This data gets loaded and refreshed based on the input files, and is a source to populate Data Marts.  We are using SQL Server database as a data warehouse.
​Data Marts ​Data Marts are sitting on the SQL Server Analysis Server, and are populated based on the data from Data Warehouse.  They are used for generating dashboard reports in SharePoint.
​Dashboards ​Dashboards are sitting in PerformancePoint, which is a component of Enterprise SharePoint implementation.  They consist of pages, reports, filters and other objects, that get generated based on the data in Data Marts.
 

  

File System


This section describes the file system arrangement, as given in the table below:

 

​Directory/File​ ​Description
​PulseBI ​The main directory containing programs for PulseBI implementation.
​PulseBI\bin ​The directory containing executables.
​PulseBI\bin\PulseBI.exe ​The main PulseBI program, used to refresh the data warehouse and data marts.
​PulseBI\bin\CsvDataReader.dll ​The library containing utility functions to load CSV files.
​PulseBI\cfg

​The directory containing PulseBI configuration.

​PulseBI\cfg\Cube.csv ​The CSV file containing cube configuration.
PulseBI\cfg\CubePartition.csv
​The CSV file containing cube partition configuration.​
​PulseBI\cfg\Dictionary.csv ​The CSV file containing dictionary configuration.
​PulseBI\cfg\Dimension.csv ​The CSV file containing dimension configuration.
​PulseBI\cfg\DimensionPartition.csv​​The CSV file containing dimension partition configuration.​
PulseBI\cfg\Files.csv​​​​​The CSV file containing staging file configuration.
​PulseBI\cfg\Lookup.csv ​The CSV file containing lookup configuration.
​PulseBI\cfg\Mart.csv ​The CSV file containing data mart configuration.
​PulseBI\cfg\Partition.csv ​The CSV file containing partition configuration.
PulseBI\cfg\​Pulse_BI_Data_Dictionary.xlsx ​The Excel spreadsheet containing PulseBI configuration.
​PulseBI\cfg\Settings.xml ​The XML file containing system settings.
​PulseBi\cfg\Archive\Folder containing an archival copy of each schema revison as it was loaded into the warehouse.​
​PulseBI\<Datamart>_dashboard ​The directory containing dashboard extracts, modified based on the configuration for a <datamart>.
​PulseBI\dashboard_src ​The directory containing dashboard source files.
​PulseBI\log ​The directory containing PulseBI logs.
​PulseBI\schema ​The directory containing PulseBI schema scripts.
​PulseBI\schema\Config.sql ​The file containing schema scripts used to create configuration tables.
​PulseBI\schema\<datamart>_Staging.sql ​The file containing schema scripts used to create staging tables for the specified data mart.
​PulseBI\schema\<datamart>_Dimensions.sql ​The file containing schema scripts used to create dimension tables for the specified data mart.
​PulseBI\ssas_<datamart> ​The directory containing the SSAS project, modified based on the configuration for a <datamart>.
​PulseBI\ssas_src ​The directory containing source SSAS project.
​PulseBI\<datamart> ​The directory containing CSV input files for specified data mart.
PulseBI\<datamart>\Archive\<datamart>​\YYYYMMDD_HHMMSS ​The directory containing any CSV Files that have been staged and -Archive Yes was specified.
 

 

PulseBI Program


This section describes the PulseBI program.  PulseBI program is responsible for loading the data warehouse configuration, generating schema scripts, and refreshing data marts as specified.  The program should be executed in the following way:

PulseBI -param1 "param1 value" -param2 "param2 value" ...

The parameters to the program are given in the table below, with the default value printed in bold:

 

​Parameter​ ​Values ​Decription
​-Config Yes, No ​This parameter indicates whether to reload the data warehouse configuration from the PulseBI\cfg directory into the data warehouse. 
  • "Yes" loads the configuration
  • "No" does not load the configuration.
​​-ConfigTargetFile, Database​This parameter is only valid if -Config is active. If "Database", the CSV files corresponding to the table names will be applied directly to the database.
​-Generate ​Yes, No ​This parameter indicates whether to generate schema scripts and sample input files for the data warehouse. 
  • ​​"Yes" will generate the scripts or modify the files specified by 'GenerateItem'.
  • "No" will not generate the scripts or modify the files specified by 'GenerateItem'.
​-GenerateItem All, Config, Schema, Input, SSAS, Dashboards ​This parameter indicates which item will be generated, and is only used if -Generate is set to "Yes". 
  • "All" will process all items (equivalent to specifying 'Config', then 'Schema', then 'Input', then 'SSAS; and finally specifying 'Dashboards'.
  • "Config" will create the configuration tables 
  • "Schema" will generate the schema scripts for data marts.
  • "Input" will generate sample input files.
  • "SSAS" will process and modify the SSAS project for a DataMart based on the configuration.
  • "Dashboards" will process and modify the dashboard exports will be updated based on the configuration.
​-GenerateTarget File, Database ​This parameter is only valid if -Generate is active, and only for "Config" and "Schema" items.  If "File" is chosen, the schema scripts will be saved to the files.  If "Database", the schema commands will be applied directly to the database.
​-GenerateMart All, <datamart> ​This parameter is only valid if -Generate is active, and only for "Schema" and "Input" items.  If "All" is selected then all data marts will be generated, otherwise only the specified one.
​-Refresh ​Yes, No ​This parameter indicates whether to refresh the data warehouse. 
  • "Yes" will refresh the specified Datamart.
  • "No" means the Datamart will not be refreshed.
​-RefreshMartGroup​All, <datamartgroup>, Ignore​This parameter allows Pulse BI to refresh the DataMart(s) associated with the Group in the Mart Tab of the Data Dictionary.
​-RefreshMart All, <datamart> ​This parameter specifies which data marts will be processed, and is only used if -Refresh is set to "Yes".  "All" means all data marts will be processed, whereas if <datamart> is specified only that data mart will be processed.
​-RefreshStaging ​Yes, No ​This parameter is only valid if -Refresh is active, and specifies whether the staging part of the data mart refresh will be processed.
​-RefreshDimensions ​Yes, No ​This parameter is only valid if -Refresh is active, and specifies whether the dimension update part of the data mart refresh will be processed.
-Refresh​SSAS ​Yes, No ​This parameter is only valid if -Refresh is active, and specifies whether the SSAS update part of the data mart refresh will be processed.
​-DebugSQL Mart, Config, All, No ​This parameter will print out Increasing amounts of detail about the SQL that gets executed by PulseBI program.
  • "Mart shows the SQL that is used to Create and/or update DataMart Information (Merge statements mostly)
  • "Config" shows the data displayed by "Mart" (above), as well as back-end SQL queries used by Pulse BI (ALL selects into CFG_* tables et al.)
  • "All" shows all SQL queries ( e.g. all from "Config") as well as the ActionLog actions (inserts mostly). This comprises ALL actions the Pulse BI takes.
-Archive​Yes, No​​This parameter is only valid if -RefreshStaging is active (=Yes). Specifiying "Yes" will move each of the processed files to the Archive folder, while "No" will leave those processed files in the Staging location.
​-LogToScreen, Log, Both​This parameter specifes werhee the executed ouput is sent.
  • "Screen" will show actions on the screen only.
  • "Log" will send all output to a unique file in the Log Folder only.
  • "Both" will send the output to the screen and to a unique file in the Log Folder.
​-Help ​If specified, the program will print the syntax on how to run PulseBI program.
 

 

Internal Functions


This section lists and describes the internal functions used by the Pulse BI Application, grouped by its internal use:


Overarching Functions: These 3 Parent Functions execute the requested actions based on the passed-in parameters.​

​​Functio​n Name​Description
generate()​Parent Function. Executed the child functions when passed paramters include 'Generate -Yes'
​generate_config()​​​
​generate_schema()
​generate_input()
​generate_ssas()
generate_dashboards()​
refresh()Parent Function. Executed the child functions when passed paramters include 'Refresh -Yes'​
refresh_staging()​
​refresh_dimensions()
​refresh_ssas()
​config()​Loads the Configuration tables with the current configuration from the cfg/*.csv files when passed param include '-Config Yes'

 

Functions Below build and return a querystring for querying SQL:

​​Function NameDescription​
​GetListOfMartsOfDataMartGroups()​Returns the Marts in a Datamart (if specified) or ALL Marts 'staged' in CFG_Mart​​.
​GetListOfMarts()
​Returns all Datamarts or Specific DataMarts.
​GetDimensionsOfMarts($mart, $history)​Returns a grid of Dimensions that belong to one DataMart. Overloaded $history parameter allows for retreiving columns for 'history' in addition to 'normal' Dimenisons​.​
​CustomPartitionViewOfADimension($mart, $dim)​For Building Merge Statements - Get the Column Name for any Custom Partitions.
​GetTableColumnsForADimesionOfAMart($mart, $dim, $ColumnsOnly)​Retreives the Columns for one Dimension of a Datamart.
​GetTableColumnsForAHistoryDimesionOfAMart($mart, $dim, $FilterWhere)​Gets columns for a history dimension based on the 'non-history' table. Overloaded $FilterWhere allows for filtering to specific history columntype​.
GetColumnsOfFileCodeOfDimensionOfMart($mart, $dim, $filecode)​​Returns the columns for one filecode for one dimension.
FileList($mart) ​Returns Columns about all Dimensions (and Filecodes) for a DataMart
ListDimensionRefreshOfMarts($mart)​Returns information related to Refresh Values for all Dimensions of a Mart.  (used only in create_Schema() )
GetHistoryFlagOnDimensions($mart)​Returns those dimensions that are flagged as having a history table
​GetRowCounts($tableName, $IsCFGTable)​Gets the count of rows inserted (used only in generate_config() )
​GetColumnsForDataMartWithFilter($mart, $columnval)​Returns Columns from CFG_Dictionary. Overloaded $columnval filters to a specific ColumnType​.
​LookupValuesForDataMarts($ReferenceField)​Returns columns from CFG_Lookup for a specific referenceField
BuildSPRename($HeaderComment, $OriginalName, $NewName, $ObjectType)​Renames objects (INDEXES, TABLES et al) from 'Name' to 'Name-v#' where # is $vSchema.

Functions Below are supporting functions for things done during Generate or Refresh steps

​Function Name​Description​​​
​createSSASfolder($Mart, $fromIn)​Restores the SSAS_src folder to \SSAS_<Datamart>
​ProcessDSV($Datamart)​Processes the Data Source View for a Datamart
​DSVXMLCheckAndUpdate($xmlnode)​takes in an xml node structure, reviess each node for changes (Attribute to FriendlyName) and modifies it. Used in Generate_SSAS to Modify the Data Source View​
​ProcessCubeFileForAMart($Datamart)​Parent function. Read and builds the node structure in *.Cube for use by Child Funcitons.

​CubeXMLCheckDimensionsAndUpdate($Dim, $xmlnode)​​Child Function. Checks one XML Node structure in *.Cube and Modifies accordingly
CubeXMLCheckMeasureGroupsAndUpdate($Dim, $xmlnode)​Child Function. ​Checks one XML Node structure in *.Cube and Modifies accordingly

​CubeXMLCheckActionsAndUpdate($Dim, $xmlnode)​Child Function. ​Checks one XML Node structure in *.Cube and Modifies accordingly
​ProcessDimFilesForAmart($Datamart)​Parent Function. Read and builds the node structure each dimension in *.dim for use by Child Funcitons.
DimensionXMLCheckAndUpdate($xmlnode)​​Modify Dimension Source Name.
​​RefreshDimensionsForSSAS($mart, $dim)​​Check if one dimension of a mart should be refreshed.
​​ProcessPartitionsForSSAS($mart, $cube, $mg , $part)​​Check if a Partition for a MeasureGroup for a Cube for a Datamart is to be refreshed.
​ProcessCubesForSSAS($mart, $cube)​​Returns refresh status for a Cube.
​ProcessDashboardForAMart($Datamart)​Processes the <datamart>.ddwx file and modifies according to the configuration.


Functions below are general purpose functions and are used by myay sub-areas.


​Function NameDescription​​​
​MakeColumnsForView($Columns, $Type)​Takes in an array of columns (e.g. from GetAllColumnsOfDimensionOfMart) and returns a fully realized grid of '[column] as [friendlyname] -- column #
​ArchiveStagingFile($PathAndFile, $mart, $Dim)​Pass the Filename (full path), Datamart and Dimension and this will check and create the folder (full sub-path like mkdir /p) and move the file there (With overwrite)​.
​ArchiveConfigurationFiles()​Called once at the end of the Configuration Load; checks for cfg\Archive\# where # = $vSchem and moves all *.cfg files to that folder with Overwrite​.
​leftjustify() CONVERT any string (here-string, 'plain' $vars) to an array of chars, and left justify as much as possible.
DebugSQL($sql, $fg, $bg)​This function is responsible for outputing many of the SQL commands but only if '-DebugSQL' is not No. Overloaded to allow for Foreground & Backgorund Color changes as well as NoLineFeed.​
​WriteOut($display, $IsError)​This is a replacement for 'write-output' so that we can capture output to a log, screen or both. Overload $IsError to show ouptut wiht 'Error: " prepended and in Red​.
Used to ouput data regardless of DebugSQL status. 
​GetIDFromActionLog()​Returns the Next Highest numerical ID from the ActionLog
​GetVersionInfoFromConfigTable
This sets up a default value (when blank) and retreives current schema version (for newer verisons of Pulse BI).
​GetConfigStatusFromConfigTable($SchemaVersion)​Returns the status of the Configuration Load for this SchemaVersion ($vSchema)
​UpdateConfigStatus($Status, $SchemaVersion)​Set the ConfigStatus for a SchemaVersion
LogToActionTable ($DataMartGroup, $DataMart, $Dimension, $Attribute, $ActionCategory, $ActionGroup, $ActionDetail, $DimensionPrefix, $StartDateTime, $EndDateTime, $NumStaged, $NumInserted, $NumUpdated, $NumDeleted, $NumTotalRows, $NumFKeyRows, $NumLookupRows)Inserts data about actions into the Log Table (ALL_ActionLog)
LogToErrorFile($ErrorIn, $Type, $Cat, $Group, $Detail)​Logs errors to the error logfile in \logs\. Overloaded $Type allows to capture ANY message as an error​. Correctly increments $Error so all erros are captured and emailed.
​DisplayExecutedParams()Displays the Parameters as specified on the command line.
​DisplayExecutedDBParams()​Displays Pulse BI and schema information.
​CreateConnToSQL()​Creates our initial (and only) connection to the Database Engine
​ExecuteNonReadQuery($sql)Takes in a Structured Query, and outputs only a command count of rows affected
​ExecuteReadQuery($Sql, [int]$TimeOut)​Takes in a Structured Query, and outputs a datarow of output
​CheckForErrorsAndEMail()​If $Error has been incrmented for this run, sends email based on config.xml values and attaches the error logfile.



Data Warehouse


 The Pulse BI Data Warehouse is structured using the latest recommended design philosophy. It comprises Configuration, Staging and DataMart Tables and are named in a simple and straightforward way. These tables are always accessed via Views and not directly. The stucture can be sumarized as:

Pulse BI has the following Configuration Tables:

​CFG_Cube​Contains the data from the Cube tab of the Data Dictionary
​CFG_CubePartitionContains the data from the CubePartition tab of the Data Dictionary​
CFG_Dictionary​​Contains the data from the Dictionary tab of the Data Dictionary
CFG_Dimension​​Contains the data from the Dimension tab of the Data Dictionary
​CFG_DimensionPartition​Contains the data from the DimensionPartition tab of the Data Dictionary
CFG_Files​​Contains the data from the Files tab of the Data Dictionary
CFG_Lookup​Contains the data from the Lookup tab of the Data Dictionary​
CFG_Mart​Contains the data from the mart tab of the Data Dictionary​

​All data in these tables are loaded from the *.csv files generated by the Data Dictionary as located in \cfg\ folder.

Pulse BI will process data in the staging table (STG_<dimension+filecode>) to its dimension table (TBL_,dimension>) via the supporting views. There will be one Table Per Dimension + FileCode in the configuration

These are the tables the Cubes refresh their data from and the SharePoint Dashboards build their graphs and report information from, and are named TBL_,dimension>.

These views allow for Friendly Column Names amongst other flexible features. They are named either the same as the Dimension minus TBL_. e.g. The Dimension of "Class" as a Dimension table named "TBL_Class" and has a view named "Class". There is also a PART_<dimension> view created for use by '-refreshdimensions yes' when a dimension is defined as partitioned (as listed in Dimension Partition tab of the Data Dictionary).​​​​


Data Marts


This section describes the data marts in the Pulse BI implementation.

 

Dashboards


This section describes the PowerPoint dashboards in the Pulse BI implementation.