Data Warehouse documentation in SharePoint

Sample: Data Warehouse documentation in SharePoint

Overview

You can easily process any SAS output files and build automated process flows which interact with other systems. This sample creates a PDF document with SAS ODS of every table in the SASHELP library and automatically upload each file to a SharePoint document library.

In this sample we will cover the following steps:

• Start a remote SAS Workspace Server session
• Use SAS Dictionary table to list all tables in the SASHELP library
• Create a PDF report with PROC CONTENTS and PROC PRINT for each table
• Download all PDF files
• Directly upload all PDF files to a SharePoint document library

Code

$remote_filepath_for_reports = "C:\temp\DWH_Inventory_remote"
$local_filepath_for_reports = "C:\temp\DWH_Inventory_local"

Import-Module cmdlets4sas

$ws = Connect-SasWorkspaceServer "iom://server.analytical-software.eu;Bridge;SecurityPackage=Negotiate"

# get all tables in libref SASHELP
$sas_inventory = Read-SasData $ws "select * from dictionary.tables where libname = 'SASHELP' and memtype='DATA' and typemem='DATA' "

# create a pdf report for all tables with PROC contents and the first 20 observations
foreach ($table_item in $sas_inventory)
{
    $libref = $table_item.libname
    $tablename = $table_item.memname
    $remote_report_document = "$remote_filepath_for_reports\$libref_$tablename.pdf"
    $local_report_document = "$local_filepath_for_reports\$libref_$tablename.pdf"

    Write-Host "creating documentation for: $libref.$tablename"

    $sascode = 
@"
ods _all_ close;

ods pdf file="$remote_report_document";

proc contents data=$libref.$tablename;
run;

proc print data=$libref.$tablename(obs=20);
run;

ods _all_ close;
"@

    Write-Verbose "generated SAS code: $sascode"

    # execute SAS code
    Invoke-SasCode $ws $sascode

    #download report file
    Write-Host "downloading from $remote_report_document to $local_report_document" 
    Read-SasFile -Workspace $ws `
                 -From $remote_report_document `
                 -To $local_report_document `
                 -Binary
}


Disconnect-SasServer $ws






#Publish to SharePoint 2010
$server_and_site = "http://server.analytical-software.eu/SASWorkspaceTest"
$server_document_library = "DWH%20Inventory"

$wc = new-object System.Net.WebClient
$wc.Credentials = [System.Net.CredentialCache]::DefaultCredentials

# upload all reports to SharePoint
$files = Get-ChildItem $local_filepath_for_reports 
foreach ($file in $files)
{
    $fileName = $file.Name
    $uploadtarget= "$server_and_site/$server_document_library/$fileName"

    Write-Host "uploading file $fileName to SharePoint"
    $wc.UploadFile($uploadtarget,"PUT", $file.FullName) 
}

Results

SharePoint library containing the PDF files:

Sample documentation for table SASHELP.PRDSALE (german):