Table of Contents
Foreword 1
The Journey 1
Chapter 1: Introduction 5
What is OneStream? 5
Contents 6
The Life Cycle of Data and its History 8
Why use a Corporate Performance Management Platform? 9
Flexibility 9
Accountability 10
Mergers and Acquisitions 10
Speak Better to your Data 10
Operational Data 10
Costs – Rising Audit Fees 10
Leading Practices 10
Conclusion 11
Epilogue 11
Chapter 2: Methodology and the Project 13
Requirements Gathering 13
What NOT to do… 14
Consolidations 14
Data 14
Chart of Accounts 15
Reporting Perspectives 15
Foreign Currency 15
Complex Ownerships 16
Intercompany Activity 16
Cash Flow Statement(s) 16
Statistical Accounts 17
Allocations 17
Planning 17
Financial Planning Areas 17
Products and Scenarios 18
Modeling 18
Management Reporting 18
Account Reconciliations 19
Tax Provisioning 19
Task Manager 19
Other Tools 20
Designing your OneStream Application 20
Goals of the Design 20
Application Sizing 20
Processing Times 21
Task Automation 21
Task and Data Audit 21
Backups and Logs 22
Testing Methodology and Analysis 22
Unit Testing 22
Integration / System Testing 23
Data Validation 23
Centralized Data Validation 24
Validation Steps 26
Stub Period 27
Local Currency Trial Balance 28
Translated Trial Balance 28
Flows 28
Adjustments 28
Eliminations 29
KPIs 29
Allocations 29
Go-Forward Actual Results 29
Centralized vs. Decentralized 29
Rollforwards in Local Currency for Current Year 30
Budget & Forecast 30
Alternate Exchange Rate Scenarios 30
Validation of Outbound Data Integrations with Other Systems 30
User Acceptance Testing 31
Roles and Responsibilities 33
Load Testing 35
Parallel Testing 35
Entry Criteria / Dependencies 35
Datasets and Flow Models 36
Test Plan 36
Roles and Responsibilities 37
Success / Exit Criteria 37
Training and System Documentation 38
Training 38
Types and Delivery 38
Audience Determines the Approach 38
System Documentation 39
Managing the Implementation 39
Identify your Team 39
Choosing a Partner 39
Administrator 40
Define the Scope 40
Roadmaps 41
Implementation Size vs. Complexity 41
Timeline vs. Functionality 41
Stakeholder Involvement 42
The Holy Triad – Scope, Time, and Resources (Budget, People) 42
Create a Timeline 43
Requirements and Design 43
Build 44
Test 44
Rollout 44
Planning to Support Your Application 45
Center of Excellence Model 45
IT Support 45
Admin/Functional Support 45
Managing the Project 46
Why Use a Project Manager at all? 46
Methodology Type – Agile vs. Waterfall 46
Managing Risk and Change 47
Effective Communication 47
Plan for Success 47
Train Early 47
Document Processes Prior to Kick-Off 48
Staffing Before, During, and After Project Kick-off 48
Quality Assurance 48
Continuous Process 48
Minimize Rework 48
Change Management 48
Promote User Adoption 49
Tools to Use 49
Conclusion 49
Epilogue 49
Chapter 3: Design and Build 51
Design 51
Client’s Reporting/Analytical Needs 53
Designing and Building Your Model 55
Gather All Dimension Types 55
Review the Detail of each Dimension to Ensure it Meets Reporting Needs 55
Group the Dimensions to Identify Cubes 56
Consider Future Solutions and Ensure Your Design Considers other Dimensions Needed 56
Create Foundation of Workflow – Begin Refining the Workflow for each Cube within the Parameters of the Requirements 56
Identify and Plan all Data Flows into OneStream and Between Solutions within OneStream 56
Create Rules Inventory – Identify All Rules to be Built 57
Create Report Inventory – Identify All Reports to be Built 57
Plan for Training End-Users 57
Data Volumes and Design 57
What Makes OneStream Unique 57
Data Units 57
Data Unit – Level 1 58
Stored Data versus Calculated-On-the-Fly Data 60
Data Volumes 60
Data Unit – Interdimensional Irrelevance and Database Sparsity 61
Selecting Valid Account Combinations for Budget and Forecast 61
Data Unit – Performance 62
Cube Design 62
Determining the Dimensions in the Database 63
‘Unspecified’ Dimension Members 64
Cube Integration 64
Intercompany Across Cubes 65
Cube Design Options 65
Monolithic Cube (Scenario Types – 10) 65
Super Cube – Linked Cubes 65
Paired Cubes 65
Specialty Cubes 65
Some Other Cube Design Considerations 65
When Should I Use Extensibility? 66
Relational and BI Blend Design 67
Extensible Applications 69
Design Example 72
Problem 72
Resolution 72
What Makes an Application Large? 73
Prototyping a Design 73
Introduction and Purpose 73
Workflow Design 74
Overview 74
Considerations for Workflows 74
Confirmation Rules 74
Certification Rules 74
Data Loading and Considerations for Partitioning 74
Dimension Types 75
Overview 75
Considerations for Each Dimension 75
Top Customs 75
Calculation Efficiency 76
Dimension Design 76
Time Periods/Years Dimensions – Implications 76
Time Dimension Types 76
Time Dimensions: Questions and Considerations 76
Scenarios Dimension 76
Scenarios Dimension Considerations 77
Account Dimension 78
Overview 78
Typical Structures 78
Accounts Dimension Questions and Considerations 79
Entities Dimension 79
Overview 79
Entities/Organization Dimension Considerations 80
Entities/Org Dimension Considerations 80
Flow 80
Overview 80
Switch Sign 80
Switch Type 80
Flow Processing 81
Flow Processing Type 81
Is Alternate Input Currency 81
Is Alternate Input Currency for All Accounts 81
Translate using Alternate Input Currency, Input Local 81
Translate using Alternate Input Currency, Derive Local 81
Alternate Input Currency 81
Source Member for Alternate Input Currency 81
Flow Dimension Considerations 81
Other User Dimension Considerations 81
Overview 81
User-Defined Considerations 81
Dynamic Attributes 82
Conclusion 82
Epilogue 82
Chapter 4: Consolidation 83
Key Consolidation Concepts 83
Consolidation Methods 83
Consolidating 83
Non-Consolidating 84
Translation Methods 84
Periodic Method 85
Direct Method 85
Cube 85
Consolidation Algorithm Types 85
Standard 85
Stored Share 86
Org-By-Period Elimination 87
Stored Share and Org-By-Period Elimination 87
Custom 87
Performance Considerations 88
Translation Algorithm Types 88
Standard 88
Standard Using Business Rules for FX Rates 88
FX Rates 89
Entity 90
Relationship Settings 90
Percent Consolidation 90
Percent Ownership 90
Ownership Type 90
Intercompany Eliminations 90
Settings 91
Parent Currencies 91
Alternate Hierarchy 92
Auto Translation Currencies 92
Limitations 92
Things to Consider 92
Tips 93
Parent Adjustments 93
Origin 93
Adjustment Entities 94
Equity PickUp 95
Ownership Percentages 95
Calculation and Consolidation Passes 96
Org-By-Period 96
Considerations 97
Flow 98
Periodic vs. YTD Data Loads 98
YTD 98
Periodic 99
Historical Rate Overrides 100
Using Historical Exchange Rates 100
Using Historical Values 100
Out of the Box: Account and Flow Member Configuration 100
Custom Override: Account and Flow Member Configuration 101
FX and CTA 101
Cash Flow 102
Rollforwards 102
Flow Configuration 103
Switch Type 104
Flow Hierarchy Configuration 104
Calculation Approach 105
Text Field 106
Other Consolidation Topics 106
Discontinued Operations 106
User-Defined Dimension 106
Acquisitions 108
Beginning Balances 108
Pro forma 108
User-Defined Dimension 108
Scenario 109
Constant Currency 109
Scenario Settings 109
Translation Rules 110
User-Defined 110
Scenario 111
Conclusion 111
Epilogue 112
Chapter 5: Planning 113
Designing Planning Solutions in OneStream 113
Approach to this Chapter 114
What is Planning? 115
Specialty Planning 115
Operational Planning 115
Designing & Building Planning in OneStream 115
Classifying Planning Projects 115
A Note on Actuals 116
Planning Design Principles 116
Design Approach 116
Business Process Design 117
Plan Types 117
Budget 117
Forecast 118
Long-Range 118
Operational 118
Planning Time 118
Preparation Cycles 118
Input Frequency 118
Plan Duration 118
Plan Input View 118
Plan Methods 119
Driver-Based Planning 119
Factor-Based Planning 120
Zero-Based Planning 120
Transaction-Based Planning 120
Plan Data Sources 121
Plan Process Flow 122
Conclusion 122
Technical Design and Build 122
Defining the Data Model 123
Picking the Right Tool for the Job 123
Cubes and Dimensions 124
Scenarios 125
Scenario Types 125
Naming Conventions 125
Input Frequencies 126
Examples 126
Example 1: Yearly Budget 126
Example 2: 12-Month YTG Forecast 127
Example 3: 12-Month Rolling Forecast 128
Example 4: Five-Year Long Range Plan 129
Versions 131
What-if Scenarios 132
Determine Reporting Dimensions 133
Determine Entity Dimension 134
Align the Entity Dimension Across Scenario Types as Much as Possible 134
Submission Process and Workflow 134
Performance 135
Data Copying 136
Translation 136
A Note on Consolidation 136
Conclusion on Entity Dimension 136
Refine your Dimension Matrix 137
Cubes and Extensibility 138
Types of Extensibility 138
How to Apply Extensibility 139
Example 1: One Cube – Extended by Scenario Type 139
Example 2: Separate Cubes – Consolidation & Planning Cube 140
Example 3: Hybrid 140
Conclusion on Extensibility 141
Specialty Cubes 141
Specialty Planning Solutions 141
Staging Tables 142
Custom SQL Tables 142
BI Blend 143
Conclusion on Data Model 143
Business Rules and Calculations 144
Determine Rule Types 144
Custom Calculate versus Calculate 144
First, a Bit of History 144
What Exactly is the Difference? 145
Using Custom Calculate Rules 145
Data Clearing 145
Durable Data 145
Linking to Workflow 145
Using Parameters & POV Members 146
Calculation Examples 146
Driver-Based Planning Calculations 146
Api.Data.Calculate 146
Eval Function 149
Calculation Drilldown 150
Factor-Based Planning Calculations 150
Data Seeding 151
Data Copying Methods 151
Data Management 151
Seeding Using Finance Business Rules 152
Location of Seeding Rules 152
Store Seeding Logic in a Business Rule 153
What’s in a Name? 153
Use Scenario Text Properties! 154
Remove the Guesswork! 154
Durable Data 155
Copying between Extended Scenarios 156
Conclusion on Seeding Rules 156
Workflow 156
Designing Workflow for Planning 156
Determine Workflow Profile Structure 157
Data Importing to the Cube 157
Data Locking Requirements 157
Determine User Types 157
Extensible Workflows 157
Determine Workflow Tasks 158
Refine the User Experience 158
Using Dashboards 158
Standardize as Much as Possible 158
Conclusion 159
Epilogue 159
Chapter 6: Data Integration 161
Data Quality 162
Staging (Data Quality Engine) 162
Non-Stage Data Tables 163
Origin Dimension 163
Assembling the Data Sources and Considerations 164
Inventory Files and Sources 164
Historical Data 166
How much Data? 166
Performance of the Data Load 166
The Quality of the Data File or Query 167
The Complexity of the Mapping Rules 167
Spreading the Data Load 167
Working through the Problems 167
Data Load Basics of Analytic Blend 168
Data Source Types 169
Data Parser and Business Rules 169
Fixed Files 169
Delimited Files 170
Connectors 170
Data Management 171
Additional Data Input Sources 171
Excel Templates 171
Forms 172
Adjustments (Journal Entries) 173
Workflow Data Protection and Layering 174
Origin by Layer of Data Protection 176
Organizing and Naming Convention 176
Attributes 177
Business Rules 177
Transformation Rules 177
Transformation Performance 179
ODBC Connectors and Rest API 180
Automation with Batch process 181
Future State – Machine Learning 181
Conclusion 181
Epilogue 182
Chapter 7: Workflow 183
Introduction 183
Workflow Review 183
Workflow Evolving 184
End-User (Hierarchical/Responsibility) 185
Automation (Lights Out, a Popular Choice) 185
Or Hybrid of the Above 185
Data Volumes and Performance 186
Loading to the Stage 186
Workflow Clusters 186
Loading from the Stage to the Cube 189
End-User Use Case 190
Workflow Items for Planning Type projects 194
Periods 194
Structure 194
Naming Conventions 194
Workflow Design 195
Automation Use Case 196
Data Loading and Considerations for Partitioning 196
Workflow Items for BI Blend 197
Tips and Tricks 197
Workflow Performance Considerations 199
One Last thing 200
Conclusion 201
Epilogue 201
Chapter 8: Rules and Calculations 203
Rules Scope and How to Apply Them in an Application 203
Types of OneStream Business Rules 203
Finance 204
Parser 204
Connector 205
Conditional Rule 205
Derivative Rule 206
Cube View Extender 206
Dashboard Dataset 207
Dashboard Extender 208
Dashboard XFBR String 209
Extensibility Rules 209
Spreadsheet 210
Member Formulas and Calculation Drilldown 211
Business Rules versus Member Formulas 212
Purpose 212
Flexibility & Maintenance 212
Execution Requirements 213
Performance 213
Must-Know Financial Calculation Concepts 214
OneStream Calculation Sequence 214
Calculations Start with the Data Unit 215
Learning to Think of Calculations in Units of Data Buffers 216
Stored Calculation Techniques and Best Practices 217
Stored Calculations Using Api.Data.Calculate 217
Stored Calculations Using Get/Set Data Buffer 219
Stored Calculation Performance Considerations & Tips 221
Calculate versus Custom Calculate 224
Designing Member Lists 225
Designing Translation Rules 225
Designing Consolidation Rules 226
No Input Rules 226
Designing Dynamic Calculations and Member Formulas 227
Seeding Rule Best Practices 228
Dashboard Dataset Rules 229
Designing Dashboard XFBR String Rules 232
Designing Dashboard Extender Rules 233
Designing Extensibility Rules 236
Less Common Rule Tips 240
Conclusion 242
Epilogue 242
Chapter 9: Security 243
Framework and Environments 244
A Heads-up as you Read through this Chapter 244
Application Security 244
Security Roles 246
Application Security Roles 246
System Security Roles 247
Securing Metadata 248
Common Types of Users 249
Administrators 249
View All Users 251
Data Loaders 252
Data Loaders with Varying Access 257
View Users with Workflow Responsibility 261
Relationship Security 265
Access to Reporting for Users 267
Additional Items to Note 272
A Deeper Dive into Data Access on Cubes 272
Conclusion 273
Epilogue 273
Chapter 10: Reporting 275
Determine the Reports to Build 275
Reporting Options 276
Cube Views Overview 276
Determine Cube View Build Items 276
Establish Cube View Components to Setup 277
Organizing Groups and Profiles 280
Exploring High-Level Advanced Cube View Properties 281
Basic Build Principles 282
Cube View Performance 284
Dashboards Overview 285
Determine Dashboard Purpose and Build Items 286
Data Consumption 286
Static Analysis 286
Interactive Analytics 287
Functional Interaction and Analysis 288
Data Requirements 288
Data Display and Layouts 291
Dashboard Build Principles 293
Conclusion 294
Epilogue 294
Chapter 11: Excel and Spreadsheet Reporting 295
Understanding When to use Excel or Spreadsheet, Cube Views, Quick Views,
and Table Views 296
Excel or Spreadsheet? 297
Cube Views, Quick Views, XF Get Cells, or Table Views? 300
Cube Views in Excel or Spreadsheet 300
Quick View in Excel or Spreadsheet 303
XFGet in Excel & Spreadsheet 305
Table Views in Spreadsheet 307
So, Which One is Right for Me? 310
Using Cube Views in Excel and Spreadsheet 310
Inserting Cube Views into Excel or Spreadsheet 310
Resize Initial Column Widths Using Cube View Settings 314
Insert or Delete Rows When Resizing Cube View Content 315
Insert or Delete Columns When Resizing Cube View Content 315
Retain Formulas in Cube View Content 315
Dynamically Highlight Evaluated Cells 316
Cube Views Advanced Formatting Options 316
Order of Precedence 316
Styles, Selection Styles, Conditional Formatting 316
Hiding Parameter Prompts 322
Creating Quick Views in Excel and Spreadsheet 325
Important Preferences and The Importance of Workflow 325
Important General Preferences 327
Quick View Double-Click Behavior 327
Default Display Settings for New Quick Views 327
Default Suppression Settings for New Quick Views 328
Creating a Quick View from Scratch 328
The Importance of the POV 329
Another Way to Create a Quick View from Scratch 332
Creating a Quick View from a Cube View or Existing Quick View 334
Achieving Asymmetry in Quick Views 335
Conclusion 337
Epilogue 337
Chapter 12: Analytic Blend 339
Overview of Analytic Blend 340
What is Analytic Blend? 340
The History behind Analytic Blend 342
Why Do We Need It? 342
When Should We Use It? 345
Analytic Blend Data Model Types 346
Analytic Blend Design Considerations 347
Analytic Blend Data Sources and Integration 349
Deployment/Execution Methods 350
Workflow-Related 350
Non-Workflow-Related 350
Automate or On-Demand 350
Reporting, User Interaction, and Visualizations 350
End Audience and User Interaction 350
Data Sources for Reporting 351
BI Viewer or Large Pivot Grid 351
BI Blend-Specific Functionality 354
Conclusion 355
Epilogue 355
Chapter 13: Introduction to the MarketPlace 357
What is the MarketPlace? 357
History of the MarketPlace 357
MarketPlace Origin 359
OneStream as a Development Environment 359
MarketPlace vs. Platform Development 360
Time to Market 361
Tip of the Spear 361
MarketPlace Ties to Platform Releases 362
Development Process 362
What’s in the MarketPlace? 363
Platform Software 363
MarketPlace Store 364
Solution Builder 364
Helpful Links 366
What Makes up a Solution? 366
Dashboards 366
Dashboard Organization and Naming 366
Component Naming 368
Component Types 368
Business Rules 368
Dashboard Dataset 369
Dashboard Extender 369
Dashboard XFBR String 369
References to Other Business Rules 370
MarketPlace Access 370
Environment Considerations 370
Upgrading and Maintaining Solutions 371
Adding Multiple Applications 371
Customizing Solutions 372
Modifying MarketPlace Solutions 372
Custom Event Handler 373
Integration 373
Testing 373
MarketPlace Enablement 374
Key Takeaways 374
Epilogue 374
Chapter 14: Performance Tuning I 375
Introduction 375
Understanding Application Server Roles 375
Smart Load Balancing and Application Server Sets 375
User Concurrency (General Application Server Requirements) 377
Memory Requirements on General Application Servers with Multiple Applications and/or Large Metadata Model Applications 377
Final Notes on General Application Server 379
Stage Application Server Performance 379
Adjusting Workflow Cache Page Size 379
Understanding Transformation Rule Performance 381
Low Processing Cost Transformation Rule Types 381
Map One to One 381
Map Composite 381
Map Range 381
Map List 381
Map Mask (One Sided *) 381
Map Mask (* to *) 381
Low/Medium Process Cost Transformation Rule Types 381
Map Mask (One Sided ?) 381
Transformation Rule Types with very high processing costs 382
Map Range (Conditional) 382
Map List (Conditional) 382
Map Mask (Two-Sided – Source Values used to Derive Target Values) 382
Map Mask (Conditional) 382
Derivative 382
Map Range (Conditional) 383
Map List (Conditional) 383
Map Mask (Two-Sided – Source Values used to Derive Target Values) 383
Map Mask (Conditional) 383
Increasing Delete Performance 383
Replace Background (All Time, All Source IDs) 383
Replace (All Time) 383
Understanding Load Cube Performance 384
MaxDegreeofParallelismStage = 8 384
MaxDegreeofParallelismNoStage = 16 384
Analytic Blend Considerations 385
Overnight Batch Jobs 385
Interactive Workflow 385
Final Notes on Stage Server 385
Consolidation Application Server Performance 385
Understanding Consolidation Application Server Performance 385
MaxDegreeOfParallelism = 8 386
MaxDegreeOfParallelismNoSQL = 16 386
NumThreadsForAggregatingChildEntities= 8 386
Tools Available 387
Dedicated Data Management Server 388
Tools Available for Understanding OneStream Infrastructure and Resource Utilization 388
Environment Tool 389
Diagnostics 123 394
MarketPlace Solutions (What Application Servers Are Used?) 398
Account Reconciliation 398
Specialty Planning 399
Managing Changes in a OneStream Environment 399
Infrastructure Resource Planning 400
Conclusion 401
Epilogue 401
Chapter 15: Performance Tuning II 403
Introduction 403
Performance Planning 403
Environment Setup Factors to Consider 404
Where to Start? 404
On-Premise or OneStream Cloud 405
Application Environment Configuration 405
Final Notes 406
Application Design Impacts Performance 406
Financial Data Model 407
Data Model Size 407
Data Records and Data Unit Size 410
Anatomy of DataRecordXXXX Table 410
CubeId 411
EntityId 411
ParentId 411
ConsId 411
ScenarioId 411
YearId 411
PartitionId 411
Value Columns 412
Status Columns 412
Application Performance Case Study 414
Environment 414
What to Look Out For 414
User Count 415
What to Look Out For 415
Data Model Features 415
What to Look Out For 416
Workflow Configuration and Processing 416
What to Look Out For 416
Data Record and Data Unit Sizes 416
What to Look Out For 416
Automation of Consolidations 417
What to Look Out For 417
Reporting 418
What to Look Out For 418
Final Notes 418
Other Performance Considerations 418
Conclusion 419
Epilogue 419