I want to enable user-defined dimension fields as part of analytics in a multi-tenancy product. The UDFs can be used for filtering and group by.
The current implementation is just two giant fact tables, one with transactional facts, the other snapshot facts. The dimensions are fully de-normalized into the fact tables.
How should I design the data model to support the UDF dimensions? Wide fact tables with columns such as custom_number_15 and customer_string_10? Normalize and incorporate some kind of EAV? Are there best practices I can look at?
Edit: OLTP and OLAP are both using MySQL but are separated. Data goes into OLAP via ETL.