Category Archives: Blackboard Analytics Higher Ed

Delete the existing DetailSubPackage and copy the correct one from other server or from BlackBoard

by

We want to test PSSA on 2016. PSSA was working fine in 2012 infrastructure.

We upgrade SSIS package file Mainpackage using SSDT 2015  and started the job. We received error:

The connection “Higher Ed Analytics Data Warehouse – Managed” is not found.
This error is thrown by Connections collection when the specific connection element is not found.

On investigation we opened DetailSubPackage in SSDT 2015 and found it to be non empty.

We copied DetailSubPackage from our working 2012 server. Viola! the pSSA job started working.

select top 1000 * from Hea.EventLog
order by EventTimeStamp desc

The above query started showing the logs.

Monthly task of generating BbA HTML documentation using installed SQL Spec

by

1) Remotely log-in to the server where we have SQL Spec installed.

2) Navigate to the SQL Spec root directory, and open the SqlSpec Windows batch file in say notepad.

3) Update the following six variables for your environment:

  1. SQLSpecHome: Path of the directory containing SQLSpec.exe
  2. SQLSpecModuleHome: Path of the directory containing this batch file
  3. SQLServer: SQL Server Instance name
  4. SQLDB: Name of the module SQL Server database, e.g. iXXSA
  5. OLAPServer: Analysis Services Instance name
  6. OLAPDB: Name of the module Analysis Services database, e.g. iXXSA

4) Save and close the batch file.

5) Next, in the same directory, locate the “SQLSpec File Generator.sql” file and open it in SQL Server Management Studio.

6) Change the database of execution to the iXXSA database and execute the file.

7) The query’s execution results should be cut and pasted into the SQLSpec.config file found in the same directory. Completely replace the original contents of the file with the new configuration. This configuration will provide SQLSpec with all of the information it needs to build the documentation (the CHM file and the HTML documentation) as originally delivered in the BbAnalytics folder, C:\BbAnalytics\iXXSA Version 4.0.0\Documentation\Data Model\.

8) To execute SQLSpec and regenerate the documentation, execute the SQLSpec.bat batch file. Depending on the size of the module, it can take several minutes to several hours to complete.

 

Install SQLSpec steps

by

SQLSpec by Elsasoft, is the database documentation tool used by Blackboard Analytics to document the database objects found in the iXXSA database and one SQLSpec license is included with your Blackboard Analytics purchase.

This documentation can be regenerated at any time from your installation to stay in sync with customizations made to the iXXSA database.

The following steps will configure SQLSpec for your environment.

  1. Locate the SQLSpec files in the BbAnalytics folder, C:\BbAnalytics\iXXSA Version 4.0.0\Install\7_sqlspec\.
  2. If there isn’t a SQLSpec.exe file there, download an evaluation copy from http://www.elsasoft.org/ and copy it to this directory.

 

Execute SQLSpec.exe and click on the License Key button. If it not licensed, first look for a license.txt file in this directory and enter the license information at the prompt.

  1. After validating that SQLSpec is licensed, close SQLSpec. For Blackboard Analytics modules, a batch and configuration file is used to run SQLSpec and pass in all of the necessary parameters.
  2. Locate the htmlhelp.exe file in this directory and execute it.
  3. This will guide you through installing HTML Help Workshop 1.3, which includes an HTML Helpfile compiler – used to compile the HigherEd Analytics.CHM file discussed in the Data Model Review section of this Installation Guide.
  4. Click Next through the steps to complete the installation.
  5. If you receive an error message saying that the computer already contains a newer version of HTML Help, you can ignore it. It is the compiler, hhc.exe, that is needed for SQLSpec.
  6. Next, navigate into the iXXSA directory.
  7. Open the SQLSpec.bat batch file for editing in Notepad.
  8. Update the following six variables for your environment:

 

  1. SQLSpecHome: Path of the directory containing SQLSpec.exe
  2. SQLSpecModuleHome: Path of the directory containing this batch file
  3. SQLServer: SQL Server Instance name
  4. SQLDB: Name of the module SQL Server database, e.g. iXXSA
  5. OLAPServer: Analysis Services Instance name
  6. OLAPDB: Name of the module Analysis Services database, e.g. iXXSA

12. Save and close the batch file.

13. Next, in the same directory, locate the “SQLSpec File Generator.sql” file and open it in SQL Server Management Studio.

14. Change the database of execution to the iXXSA database and execute the file.

15. The query’s execution results should be cut and pasted into the SQLSpec.config file found in the same directory. Completely replace the original contents of the file with the new configuration. This configuration will provide SQLSpec with all of the information it needs to build the documentation (the CHM file and the HTML documentation) as originally delivered in the BbAnalytics folder, C:\BbAnalytics\iXXSA Version 4.0.0\Documentation\Data Model\.

16. To execute SQLSpec and regenerate the documentation, execute the SQLSpec.bat batch file. Depending on the size of the module, it can take several minutes to several hours to complete.

 

Blackboard Analytics Code Snippets

by

/****
* Snippet: Load a Dimension
*/
exec HEA.ProcessEntity @EntityCode = ‘DimSample’
go
/****
* Snippet: Load a Dimension and display the final Manifest
*/
exec HEA.ProcessEntity @EntityCode = ‘DimSample’, @DebugMode = 1
go
/****
* Snippet: Load a Fact Table
*/
exec HEA.ProcessEntity @EntityCode = ‘FactSample’
go
/****
* Snippet: Load a Fact Table and display the final Manifest
*/
exec HEA.ProcessEntity @EntityCode = ‘FactSample’, @DebugMode = 1
go
/****
* Snippet: View current Extract SQL for a Source table
*/
select [Value]
from HEA.Clause
where DomainCode = ‘Full Extract’ and TargetTableName = ‘SOURCE_TABLE’
order by Ordinal
/****
* Snippet: Rebuild the SQL extract clauses based on new metadata
*/
exec HEA.BuildClauses @DomainCode = ‘Full Extract’, @TargetTableName = ‘SOURCE_TABLE’
/****
* Snippet: Extract a Source table
*/
exec HEA.ExtractSourceData @DomainCode = ‘Full Extract’, @TargetTableName = ‘SOURCE_TABLE’
/****
* Snippet: View the dimension and fact manifests from the last load
*/
declare @EventLoadCycleKey int
select @EventLoadCycleKey = max(EventLoadCycleKey) from HEA.EventLoadCycle
select EventLogKey, EventTimeStamp, Message, Manifest
from HEA.EventLog where EventLoadCycleKey = @EventLoadCycleKey and TypeCode = ‘Transform Completed’
go