Hi
Can anyone share documentation of the structure/make up of the GLTOT Cube?
Maraye
I would like to replicate the cube in SSRS for ease of reporting for the business. Any information on the structure behind the cube would be greatly appreciated
Unfortunately theres nothing like a data dictionary but its not too complicated.
The main BusinessClass is GeneralLedgerTotals. Youll then need to grab all the business classes related to each of the 20 dimensions that you want/need.
The names should be pretty self explanatory (ie Finance Dimension 1 is FinanceDimension01). Youll need some mapping tables to replicate the hierarchies. For instance all the FinDim dimensions have a Structure and Hierarchy business class (FinanceDimension01Structure and FinanceDimension01Hierarchy), if youre using Accounting Entity Groups youll need to grab the related table there as well. When it comes to the Account dimension it get a bit more tricky. Youll need to grab the Reporting tables if you want more than just posting accounts (ReportingChartAccount and ReportingChart) and do some self joining to replicate the parent/child relationship. With SSAS the mapping of these should be pretty straightforward.
Calendar is the biggest pain, the way they store dates is strange, its a string using the year concatenated to a Julian Day of the Year so you have to do a bit of trial and error when converting to an actual date. it also depends on how your calendar was built by your implementation team. Theres several tables youll need all starting with GeneralLedgerCalendarPeriod.
Depending on if youre querying the DataLake or going directly against the API the names are prepended with "FSM_".
Hope this helps,
Matt
Thanks Matthew. Much appreciated
Has any one ever done this before
