>if you have to do crazy gymnastics to do your analyses then our schema design needs work.
Yeah I figured out that if the query is over say 50 lines long and has weird marks everywhere (say DISTINCT with some windows function) then it's pretty much a bad database design.
Then I move to the data side and figured out that it actually also has something to do with the original raw data sent by the app.
So eventually _someone_ has to take the burden and do the gymnatics and frankly it totally depends on who is closer to the CXO. In our company the developers are in favor so they can do whatever they want.
>Hopefully you're already doing that sort of design, but my experience is that disciplined schema design for effective analysis is incredibly rare, despite it being an old, crufty, well established and documented area ala Kimball and old school Enterprise Data Warehousing and BI. Not all of it translates perfectly to the world of Big Data(TM) but the fundamental concepts of designing such that your data can be turned into easily queryable and useful measures and dimensions still very much applies.
EXACTLY what I'm feeling now. We are trying to work out a DWH on top of the raw telemetries (basically some simple transformations and routing done by Spark from HQ). However I really think that Kimball doesn't fit very well with the big data thing. I don't know how to say it clearly because I just moved to the data team for a few months so I'm a total newbie and everything I _feel_ could be 100% BS. I think we should start from business requirement (i.e. from our data analytics lords), and then bend the rules (Kimball will require a LOT of extra joins if what I'm reading it correctly) and keep the DWH tables sort of flattened but in a way that makes more sense than the original raw telemetry tables.
I'm not in control so I don't know what it will result into.
>For your example, the correct correlation ID completely depends on the context in which you need to do your analysis, and that should be ideally baked into your telemetry schema where applicable (and where it makes sense from an architectural/performance/data volume perspective), but if not, then baked into your intermediate ETL schema such that it is easily accessible for analysis. Perhaps it's a contextual or global incrementing index that more easily allows you to get the "latest" transaction per context (maybe a session or a match in your game, or an encounter, or outside of games, a shopping cart checkout flow), maybe your client passes more contextual information to the microservice to allow you to more easily hierarchically partition into your context. Maybe if you don't have that level of control downstream (like in a 3rd party service for example) you have an ETL or streaming job generating the global or local context from the raw telemetry that makes it easy to join against.
It's...not really easy to come up with an intermediate "common context" id sometimes. Session Id doesn't work because each session takes a lot of time and nothing else really are real-time. Since I'm the only guy in the company for requirements taking (I really think developers should have their own business analyst), it's pretty painful that each time I have to fight both sides. I have to ask the DA team to relax their requirements (realtime? No it's not possible, how about same session?) and ask the developers to bestow more connection fields (if you have this field DA will be really happy), thus the vent above :D