Best practices for analyzing/reporting database with 'flexible' schema

0
votes

I am given a task to create views (Excel, websites, etc. not database 'view') for a SQL Server table with 'flexible' schema like below:

Session(guid) | Key(int) | Value(string)

My first thought is to create a series of 'standard' relational data tables/views that speak to the analysis/reporting requests. They can be either new tables updated by a daemon service who transforms data on a schedule, or just a series of views with deeply nested queries. Then, use SSAS, SSRS and other established ways to do the analysis and reporting. But I'm totally uncertain if that's the right line of thinking.

So my questions are:

  1. Is there a terminology for this kind of 'flexible' schema so that I can search for related information?
  2. Do my thoughts make sense or they're totally off?
  3. If my thoughts make sense, should I create views with deep queries or new tables + data transform service?
2
sql-serverdatabasessasdatabase-schema
key value pairs. seems easy to the initial designer - complete pita to you the poor guy who has to answer actual questions. – Randy
for fun, you may look up SPARQL and triplestores... your data may be well suited. – Randy

2 Answers

1
votes

I would start with an SSAS cube to expose all the values , presuming you can get some descriptive info from the key. The cube might have one measure (count) and three dimensions for each of your attributes.

This cube would have little value for end users (too confusing), but I would use it to validate whether any particular data is actually usable before proceeding. I think this is important because usually this data structure masks weak data validation and integrity in the source system.

Once a subject has been validated I would build physical tables via SSIS in preference to views - I find them easier to test and tune.

0
votes

Finally found the terminology - it's called entity-attribute-value pattern (EAV) and there are a lot of discussions and resources around it.