Data Integration

251 Topics

Join Framework table data to Application table data using SQL
SOURCE: ONESTREAM CHAMPIONS Hi - I am trying to create a journal entry summary report, and am able to get most of my data from the application tables, however the created by, submitted by, etc user data are all just the unique identifiers, and not actual user names. Currently I only know of user names being kept in the Framework database location, in the SecUser table (or similar ones). Is there specific syntax I can use that will allow me to read from that database location (Framework) while running an SQL query capturing the rest of my data in the application database location through a dashboard data adapter? Or does this need to be done using a business rule to combine two tables? Thanks.
Tom_R
3 years ago Place Workflow and Data Integration
11KViews
0likes
22Comments
FDX methods (like FdxExecuteCubeView) ... Why are they faster?
Recently I heard in these forums that FDX methods (likeFdxExecuteCubeView) should be used from a BR whenever the performance of the data is critical. Can someone help me understand why they are faster? We've already built quite a lot of client-integration software using cubeviews. The cubeviews are being executed via the REST api. We are retrieving data with "api/DataProvider/GetAdoDataSetForCubeViewCommand". Now that I have discovered the "FDX" methods, it puts me in the troublesome position. I realize that a ton of our integration software should probably be re-written to use those FDX methods. The re-write may take some time. Why won't onestream just extend the "normal" cubeview interface of the REST api, and add an option to run it in FDX mode? That would be similar to the "turbo" buttons they used to put on a the tower of a PC to make it run faster (for those users who had a preference between a fast computer and a slow one.) It would be helpful to understand the differences between these two (fast and slow) mechanisms for retrieving data from cube views.
Solved
dbeavon
3 years ago Place Workflow and Data Integration
9KViews
1like
15Comments
REST API USE
Hello OneStream Community, I'm working on how to implement a REST API in OS, but i'm not sure to understand all the steps, i've already read the documentation and i've found it very hard to understand, can anyone please explain how can we implement it in OS what are the steps to follow, i started by downloading and installing POSTMAN for the tests but i don't know what should do i do after, please any help will be much appreciated. I'm also woundering if there any data management that should be used for this Thanks and merry christmas.
hoaphan
3 years ago Place Workflow and Data Integration
9KViews
0likes
10Comments
Import Data Extracted via data Management using Extensibility Business Rule
Hello - we would like to move specific Forms and Adj data from one application to another - data are extracted via Data Export in DM, does anyone have an example of the Extensibility Rule (BRApi.Finance.Data.SetDataCellsUsingUsingCsvFile) to load the extract data? Many thanks - Wei
Solved
Wei
3 years ago Place Workflow and Data Integration
8.3KViews
1like
18Comments
Unable to get value from another column in Parser BR
OneStream V7.0.1 We have a Data Source that takes data file that has 12 data columns Oct ... Sep. The year for the data in the 12 data columns is a field "Fiscal Year", the first column of the data file. We have a Parser BR that is called on each of the 12 data columns that looks at the first field in each record to get the year and concatenates that with the M<number> month for each month column to give the correct Time dimension member in which to store the data. e.g. 2023M1 The problem is we're getting the row header "Fiscal Year" instead of the year value in the record, so the string being returned looks like Fiscal YearM1 How do we get the year data from the first column instead of the column header? Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As ParserDimension, ByVal args As ParserArgs) As Object Dim fiscalYear As String() = {"Oct","Nov","Dec""Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep"} Dim YearColPosition As Integer = 0 Dim year As String = String.Empty Dim month As String = String.Empty Try 'Both of these keep giving us the column header "Fiscal Year" 'instead Of the value In the currently parsed record year = api.Parser.DelimitedParsedValues(YearColPosition) 'year = args.Line.Split(",")(YearColPosition) 'this marries the index of the month name in the months array 'with M to give us the M1 notation for period. month = $"M{Array.IndexOf(fiscalYear, args.Value)+1}" BRApi.ErrorLog.LogMessage(si, $"Time: {year}{month}") Return $"{year}{month}" Catch ex As Exception Throw ErrorHandler.LogWrite(si, New XFException(si, ex)) End Try End Function
Solved
RobbSalzmann
2 years ago Place Workflow and Data Integration
8.2KViews
2likes
23Comments
Automation of workflow using connector BR
We are loading data using SQL connector business rule and it works fine manually. Now looking for a way to load it automatically using a extender business rule and then calling from DM step. All documentation in design ref guide point to using the following code. This works when loading from a file ( with specific file format) and the file needs to be in harvest folder. I can also make it work by keeping a file which is blank in the harvest folder and still executing the workflow using the connector BR. But is there a better method which does not involve a file. Dim batchInfo As WorkflowBatchFileCollection = BRAPi.Utilities.ExecuteFileHarvestBatch(si, scenario, timeperiod, valTransform, valIntersect, loadCube, processCube, confirm, autoCertify, False)
Solved
royari
3 years ago Place Workflow and Data Integration
7.9KViews
0likes
13Comments
Can PowerBI be connected to OneStream to pull CV Excel reports that are saved in File Explorer?
We are currently producing and saving excel extracts of CV's into File Explorer using books/parcel service. We would like to connect PowerBI to OneStream to pick up these files from the OS managed server. Is this possible?
JACardona
3 years ago Place Workflow and Data Integration
7.6KViews
0likes
8Comments
Is there a REST API for quickviews?
I have some experience retrieving structured cube data from the REST API named "GetAdoDataSetForCubeViewCommand". However, I'd like to get ad-hoc information. Ideally there would be another method that is analogous to retrieving data from an Excel "quickview". Did I overlook a "quickview" mechanism in the REST API? I am using the documentation named "REST API Implementation Guide", but it doesn't mention anything that looks promising. Perhaps there may be some undocumented approach? The cubeview already does much of what I need. However it requires preparation (the cubeviews must be prepared ahead of time and deployed to the server). It would be better if the API allowed "quickviews", since they don't require any previous preparation or infrastructure. Currently we are using Onestream v.6.3.0.
Solved
dbeavon
3 years ago Place Workflow and Data Integration
7.5KViews
0likes
10Comments
Automating WF Execution
Hi all, I have connector rule set up to an external source table that is working fine to pull data into stage, but I would like to automate the execution of the workflow as well so that the data in the cube is refreshed nightly. I found the below api but it looks to me like it's meant to load a flat file into stage via an extensibility rule, not from an external table. I'm not sure how to connect it to the connector/import step I want to automate. Any thoughts? Dim WFTime As String = TimeDimHelper.GetNameFromId(api.WorkflowUnitPk.TimeKey) Dim results As WorkflowBatchFileCollection = BRApi.Utilities.ExecuteFileHarvestBatch(si, "Actual", WFTime, True, True, True, True, False, False, False)
Solved
sahilp
3 years ago Place Workflow and Data Integration
7.2KViews
0likes
6Comments
Create Table As Select
Hi, I need to build some tables in the application database. I am using a extensibility rule with a StringBuilder and the BRAPi.Database.ExecuteActionQuery command to do that. The "Create table" command is working fine. Eg: sqlScript.AppendLine("CREATE TABLE [PayrollMapping](") sqlScript.AppendLine("[Payroll] varchar(255),") sqlScript.AppendLine("[Pay_Basis] varchar(255),") sqlScript.AppendLine("[Salary_Annual_Factor] Int") sqlScript.AppendLine(") On [PRIMARY]") But I cannot make the "Create table as select" command work. Eg this does not work (Error: Incorrect syntax near the keyword 'Select') sqlScript.AppendLine("CREATE TABLE [EBSRegister] As") sqlScript.AppendLine("Select [RegisterID], [FirstName], [LastName]") sqlScript.AppendLine("From XFW_PLP_Register") Is the "Create table as select" not a valid command for the OneStream BRAPi.Database or is there simply something wrong with the syntax above? Thank you
Solved
AndreaF
3 years ago Place Workflow and Data Integration
7.2KViews
0likes
7Comments