Browse Tag

T-SQL

Enumerating Financial Reports via Workspace database

Disclaimer : This post will talk about direct database access. Do not perform unless you are sure you know what you are doing and I’m not liable! I’m just passing along information I’ve learned in case it helps you out….
Recently, we decided that we wanted to review all of our reports to determine which ones we could drop and which ones we should keep. Over the past 7 years, things have accumulated and we literally have thousands of reports gasp between all of the backups, Work In Process, and flat out duplication. In order to handle the review of this we wanted to assign prople to review specific sections of the reports. I was asked to provide a nice excel ‘report’ that would break down the report / folder hierarchy so that we could assign it.
That is when I realized that I really couldn’t locate a tool / report that would generate such a report listing!
In the old 7.2.5 version of Reports, it was relatively easy to get this information as all of the report information (including actual report content) was stored in a single database table.
With System 9 and Workspace, this is no longer true. With Workspace, the files are stored on the server’s file system in a manner consistent with the hierarchy of your reports/folders. Intially I thought I would be able to use this file system to build my report using simple DOS commands. (i.e. dir /s > reportlist.txt) Unfortunately though, the file names of the reports and folders are not ‘human readable’. (In order to decipher it, you’d have to go into the workspace database and x-ref these names to a table, etc)  Fortunately; however, you can build a report listing via SQL. The script below recursively builds a report with the following output :

(NOTE: Actually the script includes more columns of output; however, for display purposes here, I’m only showing the name and description)



Report Name     Description
AnnualReports NULL
===Actg-YContractual Obligations – Multiple Entities Contractual Obligations
===Annual Validation Summary Annual Validation Summary
===Customers and End User Markets NULL
====== 001 – End Use Markets Book End Use Markets Book
====== 002 – Top Customers Book Top Customers Book
====== 010 – Customers Book Reports – Corp NULL
========= Revenue by Top Customers – All Sectors – Pr Yr Comp Customer Revenues
========= Revenue by Top Customers – All Sectors – Pr Yr Comp -TEST Customer Revenues
====== 020 – Markets Book Reports – Corp NULL
========= Revenue by End User Markets – All Sectors End Use Markets
====== 030 – All Sectors – End Use Markets – Pr Yr Comp NULL
========= End Use Markets – Pr Yr Comp Customer Revenues

 


SQL 2005+ Query
USE BIPLus_prod;

GO

WITH Reports (PARENT_FOLDER_UUID, CONTAINER_UUID, NAME, DESCRIPTION, Level, Sort, CREATION_DATE, LAST_MODIFIED_DATE)

AS

(

— Anchor member definition

SELECT d.PARENT_FOLDER_UUID, d.CONTAINER_UUID, d.NAME, d.DESCRIPTION, 0 as level, cast(NAME as nvarchar(1024)) as sort, d.CREATION_DATE, d.LAST_MODIFIED_DATE

FROM V8_CONTAINER as d

WHERE d.PARENT_FOLDER_UUID = ‘0000011b42b29e00-0000-0b4d-c0a8e22a’ UNION ALL

— Recursive member definition

SELECT d1.PARENT_FOLDER_UUID, d1.CONTAINER_UUID, d1.NAME, d1.DESCRIPTION, Level + 1, cast(Sort + ‘|’ + d1.Name as nvarchar(1024)), d1.CREATION_DATE, d1.LAST_MODIFIED_DATE

FROM V8_CONTAINER as d1

INNER JOIN Reports AS r

ON d1.PARENT_FOLDER_UUID = r.CONTAINER_UUID

)
— Statement that executes the CTE

SELECT replicate(‘ ‘,Level) + NAME, DESCRIPTION, CREATION_DATE, LAST_MODIFIED_DATE, Level, PARENT_FOLDER_UUID, CONTAINER_UUID FROM Reports order by sort
GO


How this works :
The table V8_CONTAINER holds the main part of the report / folder entry data. In this table you will find Names, Creation Dates, Modified Dates, Owner name, UUID, and Parent UUID information. In order to build a hierarchy, you will need to pay careful attention to the UUID fields. CONTAINER_UUID is the internal name of the object in Workspace. (i.e. non-user friendly name) PARENT_FOLDER_UUID is a reference back to the item’s parent.
With this in mind, if you know the UUID of your starting point, you can simply recurse from there to get a listing of all folders and subsequent reports. In the script above I start off looking for an entry that is the starting point of our reports that we wanted to audit. For your purposes you will need to change this to what your starting point is.

NOTE : If you want to get everything in the table, use REPORTMART. REPORTMART is the parent to everything in the table.
I hope my walk through wasn’t terribly boring and helpful. I apologize for only providing a script that works in SQL Server; however, that is what we use at our site and there should be enough explanation here for someone relatively proficient with Oracle to write a similar script if they desire.

Synchronizing FDM (9.3.1) Applications w/ T-SQL

At my current location we have 1 main and 2 supporting FDM (9.3.1) applications. The main app is used to load production G&L data, and the supporting apps are used for loading our Budget and Forecast data and all are separated to ensure that a mistake in a multi-load template is minimized.
The problem we have with this setup is that we have to do 3x the maintenance work when it comes to users and locations.

While the users and locations are the exact same in each application, there are some differences; however: a.) Data Maps in Budget / Forecast are simply * to * as we use multi-load templates that have hte actual HFM account/entity names in them whereas production maps account for each company’s GL b.) Categories are different as we limit each FDM app to only the exact Category (Scenarios) required. c.) Periods are different as we limit each FDM app to only the needed periods required.

The solution for me was to implement a SQL direct copy routine for the information I need to move. This helps as I can : a.) automate this task b.) copy only what I want c.) Perform the task relatively quickly.

While the Workbench offers the ability to import / export components, I ran into trouble with it not correctly importing all of the data and it also restrcted my flexibility. Copying the entire database was not viable either as there were pieces I did not want to update….
The script below illustrates how to perform a copy via SQL. *bold*Please note that if you want to use this (or base a script off of this) that there’s absolutely no warranty and its use at your own risk.bold I’m fairly comfortable that this works fine (as I’ve tested this in our Dev), but you should test it on your own as well.

Also note, that this script does not copy ALL tables as I didn’t need them all for my purpose.

The script performs the following :

– Clears data from Target Database – Data Archives – Data Maps – Data
– Logs – Import Groups/Items – Validation Groups/Entities – Users / Partition Security – Partition Hierarchies / Links – Partitions (locations)
– Copies data from Source Database: – Import Groups / Items – Validation Groups / Items – Partitions (locations) – Partition Hierarchy / Links – Users / Partition Security
– Updates Parent Location setting on all Data Load locations (This is since I want all of my data load locations in the Budget / Forecast apps to use a * to * map. I added a new location in the Prod Database which is defined with the * to * mappings and when I copy everything over, I want the locations to use this map instead of their production G&L map.

-=-=-==-=-=-===-=-=-=-=-=-=-=-===-=-=-=-=-=-=-=-=-=-

-- FDM Database 'Auto Copy'
-- Author : Charles Beyer
-- Date : 7/1/2010
-- Description : This script is used to automatically / manually sync up database between a SOURCE FDM application and a 
--                DESTINATION FDM application.
-- NOTES
-- ##TARGETDB## - Replace this with the name of the Database that you want to SYNC 
-- ##SOURCEDB## - Replace this with the name of the Database that is the data source

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

--Clear User Security
TRUNCATE TABLE ##TARGETDB##.dbo.tSecUserPartition
TRUNCATE TABLE ##TARGETDB##.dbo.tStructPartitionLinks
delete from ##TARGETDB##.dbo.tStructPartitionHierarchy
TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
TRUNCATE TABLE ##TARGETDB##.dbo.tDataCheck

--Attempt to clear out the tDataMapSeg tables
EXEC sp_MSForEachTable '
  DECLARE @TableName VarChar(100)
  Set @TableName = PARSENAME(''?'',1)
  IF  left(@TableName,8) = ''tDataMap''
      TRUNCATE TABLE ##TARGETDB##.? 
'  
GO

--Attempt to clear ou the tDataSeg tables
EXEC sp_MSForEachTable '
  DECLARE @TableName VarChar(100)
  Set @TableName = PARSENAME(''?'',1)
  IF  left(@TableName,8) = ''tDataSeg''
      TRUNCATE TABLE ##TARGETDB##.? 
'  
GO

TRUNCATE TABLE ##TARGETDB##.dbo.tLogActivity
TRUNCATE TABLE ##TARGETDB##.dbo.tLogProcess
TRUNCATE TABLE ##TARGETDB##.dbo.tDataArchive
delete from ##TARGETDB##.dbo.tSecUser

delete from ##TARGETDB##.dbo.tPOVPartition

TRUNCATE TABLE ##TARGETDB##.dbo.tBhvValEntItem
delete from ##TARGETDB##.dbo.tBhvValEntGroup

TRUNCATE TABLE ##TARGETDB##.dbo.tBhvImpItemFile
delete from ##TARGETDB##.dbo.tBhvImpGroup

-- RECOPY Data from Prod DB to Budget DB
insert into ##TARGETDB##.dbo.tBhvImpGroup
   select * from ##SOURCEDB##.dbo.tBhvImpGroup

insert into ##TARGETDB##.dbo.tBhvValEntGroup
   select * from ##SOURCEDB##.dbo.tBhvValEntGroup

insert into ##TARGETDB##.dbo.tBhvValEntItem
   select * from ##SOURCEDB##.dbo.tBhvValEntItem

insert into ##TARGETDB##.dbo.tBhvImpItemFile
   select * from ##SOURCEDB##.dbo.tBhvImpItemFile

insert into ##TARGETDB##.dbo.tPOVPartition
   select * from ##SOURCEDB##.dbo.tPOVPartition

insert into ##	TARGETDB##.dbo.tStructPartitionHierarchy
   select * from ##SOURCEDB##.dbo.tStructPartitionHierarchy

insert into ##TARGETDB##.dbo.tStructPartitionLinks
   select * from ##SOURCEDB##.dbo.tStructPartitionLinks

insert into ##TARGETDB##.dbo.tSecUser
   select * from ##SOURCEDB##.dbo.tSecUser

insert into ##TARGETDB##.dbo.tSecUserPartition
   select * from ##SOURCEDB##.dbo.tSecUserPartition

--Attempt to import data
SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap ON
insert into ##TARGETDB##.dbo.tDataMap (PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, 
      ChangeSign, Sequence, DataKey, VBScript)
   select PartitionKey, DimName, SrcKey, SrcDesc, TargKey, WhereClauseType, WhereClauseValue, ChangeSign, Sequence, DataKey, 
       VBScript from ##SOURCEDB##.dbo.tDataMap
SET IDENTITY_INSERT ##TARGETDB##.dbo.tDataMap OFF

-- Update parent locations ...
update ##TARGETDB##.dbo.tPOVPartition
  set PartParent = 'BudgetTemplateLoc'
 where 
   PartName  'BudgetTemplateLoc' and PartControlsType = 1

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO