sql server - Best practices for analyzing/reporting database with 'flexible' schema -

sql server - Best practices for analyzing/reporting database with 'flexible' schema -

i given task create views (excel, websites, etc. not database 'view') sql server table 'flexible' schema below:

session(guid) | key(int) | value(string)

my first thought create series of 'standard' relational info tables/views speak analysis/reporting requests. can either new tables updated daemon service transforms info on schedule, or series of views nested queries. then, utilize ssas, ssrs , other established ways analysis , reporting. i'm totally uncertain if that's right line of thinking.

so questions are:

is there terminology kind of 'flexible' schema can search related information? do thoughts create sense or they're totally off? if thoughts create sense, should create views deep queries or new tables + info transform service?

i start ssas cube expose values , presuming can descriptive info key. cube might have 1 measure (count) , 3 dimensions each of attributes.

this cube have little value end users (too confusing), utilize validate whether particular info usable before proceeding. think of import because info construction masks weak info validation , integrity in source system.

once subject has been validated build physical tables via ssis in preference views - find them easier test , tune.

sql-server database ssas database-schema