OneStream

OneStream – Table Data Manager

Blog 2021 07 25 18 11 20 Window

During the month of June 2021, OneStream Software made available a new MarketPlace Solution, Table Data Manager, to which this blog will provide an introduction.  Per the Setup Instructions, “Table Data Manager was built to enable the management of tables and views in an Azure SQL Database” and based on this blog the functionality I tested works with an on-premise install also.  The minimum OneStream platform version is V.6.2.0 and to begin, download the MarketPlace solution.

Once the MarketPlace solution is downloaded, log into the appropriate application then select “Application” -> “Tools” -> “Load/Extract“.

Blog 2021 07 12 14 21 29 Window

Select the file “TDM_PV6.2.0_SV100_PackageContents.zip” downloaded from MarketPlace and then click “Open“.

Blog 2021 07 12 14 21 46 Window

At the Application Load/Extract page, select the “LoadBlog 2021 07 12 14 39 07 Window icon.

Blog 2021 07 12 14 21 59 Window

After a minute or two, the import will complete, and upon completion navigate to “OnePlace” -> “Dashboards” -> “Table Data Manager” -> “Table Data Manager“.

Blog 2021 07 12 14 47 16 Blog 2021 07 12 14 23 20 Window

From the Table Data Manager dashboard, select “Setup Tables“.

Blog 2021 07 12 14 23 39 Window

 

Select “OK” aft the solution tables are created.

Blog 2021 07 12 14 23 47 Window

Select “Launch Solution“.

Blog 2021 07 12 14 23 58 Window

The “Home” page of Table Data Manager initially has one drop-down for “Database” which when selected will list the current application you are logged into which in this circumstance is “OneStream_GolfStream_TL“, as well as databases that have been configured as external databases.  For purposes of this blog, I will continue with “OneStream_GolfStream_TL” as the database selected.  Note, the Access and Maintenance Group of the Dashboard Profile is configured to Administrators by default; therefore, non-administrators will not have access unless security is modified.

Blog 2021 07 25 17 58 15 Blog 2021 07 25 17 49 22 Window

Blog 2021 07 25 17 49 37 Window

Once the database is selected the “Home” page will render with a list of icons and functionality to select “Define, Schema, … Activity, Statistics” as well as options to select database “Tables” and “Views” plus “XFC Tables” and “XFC Views“.

Blog 2021 07 25 18 11 20 Window

If you expand “Tables” first image or “Views” second image a list of Tables or Views stored in the database selected which do not have an extension of XFC are listed.

Blog 2021 07 25 18 21 51 Clipboard

Blog 2021 07 25 18 22 10 Window

To view data in either a Table or View, select one of these objects which in this example is “vStageStourceAndTargetDataWithAttributes” then select the “DataBlog 2021 07 25 18 31 20 Window  icon.

Blog 2021 07 25 18 32 27 Window

With “Data” selected, the “Inspect Data #1” grid will list ten records “#2” by default which can be changed utilizing the drop-down.  Having briefly reviewed, Tables and View, we will review “XFC Tables“.

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

Blog 2021 07 25 18 37 12 Clipboard

Select “XFC Tables #1” which will then display “Define Tables #2” an option to “Copy #3” or create “Table Definitions #4“.  This blog will continue by reviewing the “Table Definitions” functionality of “Define” which enables defining and creation of a table.

Blog 2021 07 25 18 47 03 Window

Blog 2021 07 25 19 02 36 Blog 2021 07 25 18 57 24 Window

To begin the process of defining a table, select the “Plus” Blog 2021 07 25 19 10 18 Window icon.

Blog 2021 07 25 19 10 07 Clipboard

Key a table name which in this blog is “XFC_BlogExample” and then select the “SaveBlog 202106 10 icon.

Blog 2021 07 25 19 11 29 Window

To add “Column Definitions” to the table, select the table previously created “#1” then select the “plusBlog 2021 07 26 18 47 21 Window icon to add a row.

Blog 2021 07 26 18 45 02 Window

With a blank row added to Column Definitions, key the text for the Column Name which in this example is “Column01 #1” and then select a “Data Type #2” from the drop-down.

Blog 2021 07 26 18 49 09 Clipboard

Continue updating the Column Definitions and after the updates are complete, select the “Save #2Blog 202106 10 icon.  As this is an example, I have only added one row for the column definition.

Blog 2021 07 26 18 53 43 Clipboard

When the “Table Definition” and “Column Definition” are created, the next step is to create the table which occurs by selecting the “Create”  Blog 2021 07 26 18 58 53 Window icon which in SQL syntax generates and executes a “Create Table” statement.

Blog 2021 07 26 18 58 35 Window

Select “OK” after the create table execution completes successfully which will list the table created below “XFC Tables” as displayed in the second image.

Blog 2021 07 26 19 11 25 Clipboard

Blog 2021 07 26 19 16 09 Clipboard

With the example table created, the SQL script that created the table can be downloaded by selecting the table and then clicking the “DownloadBlog 2021 07 27 17 20 32 Window icon which is step 1 of 2.

Blog 2021 07 27 17 22 18 Clipboard

Step 2 of 2 which is to access the script to be downloaded occurs by selecting the “File ExplorerBlog 2021 07 27 17 25 09 Window icon.

Blog 2021 07 27 17 24 00 Clipboard

The SQL script created by selecting the “Download” option is stored in your user folder with the table name selected providing the prefix for the file name followed by an underscore and “CreateDDL” and ending with an extension of “xml“.  To download the file from “File Explorer“, select the file and then click the “Download Selected File’s Content FileBlog 2021 07 27 17 40 48 Window icon as displayed in the second image.

Blog 2021 07 27 17 45 38 Window

Blog 2021 07 27 17 31 47 Clipboard

Navigate to your preferred location and select “Save” to complete the file transfer.

Blog 2021 07 27 18 35 45 Clipboard

The file downloaded can be opened with SQL Server Management Studio “SSMS” or your preferred SQL Editor and the file transferred is displayed in the first image for reference.

Blog 2021 07 27 18 38 30 Window

With knowledge of the script format i.e. the same as a script, you may write with SSMS, another option to create a table is to upload a script.  In the next image, the table name was changed from “XFC_BlogExample” to “XFC_BlogSecondExample #1” and Column02 “#2” with the same data type, etc. as Column01 was added.  After the script changes, the script was saved with a file name of “XFC_BlogSecondExample_CreateDDL.sql“.

Blog 2021 07 27 18 48 24 Window

With the script updated and saved, return to “Table Data Manager” and select the “Upload” Blog 2021 07 27 18 55 26 Window icon.

Blog 2021 07 27 18 52 38 Window

Navigate to and select “XFC_BlogSecondExample_CreateDDL.sql“.  Once this is done, select “Open“.

Blog 2021 07 27 18 57 05 Clipboard

When the file is uploaded, the script is also executed as indicated in the next image.  Select “OK” to close the dialog box.

Blog 2021 07 27 18 59 56 Clipboard

After a few seconds, Table Data Manager will update and the table created is displayed which will conclude my introduction to this Marketplace Solution.

Blog 2021 07 27 19 02 15 Window

I hope this blog has been helpful.  If you have any questions, feel free to either post a comment to the blog or email me at terry.ledet@perficient.com.

About the Author

Terry is a Senior Solutions Architect at Perficient and a OneStream Certified Professional (OCP) - Lead Architect R1. He is a frequent speaker at Splash and passionate about making clients successful. When not blogging about OneStream, Terry is a husband, parent, and baseball enthusiast.

More from this Author

Thoughts on “OneStream – Table Data Manager”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to the Weekly Blog Digest:

Sign Up
Categories