I’m looking for input on data warehouse designed focused on querying/searching and not analytics. Eg. aggregations especially on time are not a concern or point of interest. Really about searching the data. Scale is small so dedicated products like elastic seem overkill (and would need to be supported by corporate IT which is a hurdle itself).
The core data is in a normalized “OLTP” type relational database. it consists of a core “product definition”, then “lots/batches” for the products and finally “measurement data” for each of the batches. the measurement data is diverse and new ones get added regularly. Also a batch can be measured multiple times for each type of measurement. eg in the hierarchy it’s 1:n for product-batches and 1:n for batches to each measurement table (tens of them, certainly less than 100 for now).
We already have a 3rd party application built on this OLTP database but it’s kinda slow. The end-user can select as many of these measurements as he has interest in and sort on any of them resulting in large amounts of joins with filtering and sorting which I assume makes it slow also due to the impossibility to create optimal indexes.
How should we structure the database for best search performance? It’s important to not loose/aggregate the individual measurements. or should we simply bite the bullet and use elastic anyway (and thereby rebuild the existing sql based tool from scratch)?