Hi all,
I have a cube view that is generated entirely by dynamic UD8 members, some retrieving text fields, returning static text/numerical data, parsing descriptions, etc. I would like to export this cube view as a .csv but I am struggling to find the best way to do this. I don't think DM Export Data jobs work with dynamic UD8s, but maybe I'm missing something. Is there a recommended method I should consider?
Would exporting your Cube View to Excel and saving the Excel Cube View as .CSV work?
Won't FDX work in this Scenario?
Yes, saving as a .csv would work but the client is insisting on automation as this export is going to drive metadata creation downstream. I'm exploring FDX now, I haven't ever used it! I know it's been around for a while but I don't think I've ever seen it implemented.
I'm trying to set up a connector with the ExecuteCubeView FDX, but I'm not sure what filter this is asking for:
I tried inputting my row filter but when I attach the connector rule to a data source, I get the following error:
Any thoughts?
I don't think you can use the OS filters there. As far as I know the filters must be like the SQL where clauses. Account IN or Account Like or Account =. What I've done is use the filter function in vb code and then use it in the filter like SQL. So something like below.
Dim lstOriginBase As List(Of String) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "Origin", "O#Top.Base", True, Nothing, Nothing).Select(Function(x) x.Member.Name).ToList()
Dim dt As datatable = BRApi.Import.Data.FdxExecuteDataUnit(si, "Sample", "E#Root.Base", "Local", ScenarioTypeId.Plan, "S#Plan", "T#" & strPlanYear & ".Months", "Periodic", True, "Account='Sales' AND Flow='Endbal_Input' AND " & SqlStringHelper.CreateInClause("Origin", lstOriginBase, True, True), 8, False)Could be a few different things causing this error. Could be the filter itself. May want to test the FDX with something simple just to see the FDX working. Then layer on the filtering. Could be the data being returned in the Cube View. For example, dates being returned. Not sure how you are testing this, but if trying to test this with the Stage tables, the Amount column is setup as Numeric data type and the majority of the other columns are set up as nvarchar. Neither of these data types are appropriate for dates. FDX route should work for you to stage this data. There is also a BRApi function called ExportCubeViewGridsToExcelFile
Dim objByte() As Byte() = BRApi.CubeViews.Process.ExportCubeViewGridsToExcelFile(si, custSubstVarsForAllCubeViews, cubeViewNamesAndVars)
Then convert from Excel to .csv programmatically. Another possible solution?