Sorry, this is strange, this is the third post, my posts are not visible. Let's give it another try. Has somebody else those issues after editing the original post?
first of all, I'm new to the community of sqlservercentral.com and I have looked through several forum posts before I decided to post my own topic :-).
I am a new employee at a research facility and responsible for all data related topics. I don't do research but I am the engineer for the new to come data platform to provide all requirements of the researcher. The most important requirements are
- self service (pick the fields I am interested it without having knowledge of the underlying data structure)
- in almost all cases data is always required on the lowest level (meaning that the researchers don't work with aggregates but want to make statistics on the previous picked data.
- Queried data sets have about 50 - 200 million records at max
I have seen a possible solution in production at my former employee. They had a star schema modelled running on a Oracle EE 11.2 RDBMS with MicroStrategy on top in ROLAP mode. Dimensions, attributes and measures have been defined in MicroStrategy and the user picked the fields in the GUI to build the report. An native SQL statement has been created by MicroStrategy and sent to the database (no idea if the GUI translated to MDX in the first place and then further to SQL).
My current employee has a very strict policy about software as we are bound to Microsoft solutions (version 2016 with the latest SPs und KB installed). Never worked with SSIS, the database and SSAS before, I did a lot of reading but I didn't find any answers to my use case. What I have learnt so far is that I must use ROLAP for storage type of the measure group and the dimensions because we have 20 Dimensions with some of them having 500 million records (as it is on a person level).
In the use case with Oracle and MicroStrategy I have seen that this can work (the generated report is automatically generated as csv file).
As I have no real world practice with Microsoft, I wanted to know if ROLAP with several dimensions and 2 dimension sizes up to 2-3 digit million records will work. Speaking in 2 dimensions, my resulting table generally will have 40-50 columns (coming from several dimensions) with a very large number of records (up to 50-200 records). Is there some limitation in the MDX language for that number of objects?
The SSAS provides the definition of dimensions and measures and the user only deals with metadata. I don't want to give up on that :-). I do not need any aggregation features which is the original intent of OLAP with pivoting but data on the primary key level of the fact table.
What do you think?
Another thing: reading the other topics: to extract a ssas query to csv I have to go through the SSIS, haven't I? Or did that change and become nicer in the newer versions?
Is there something compareable to reporting like MicroStrategy in the SSAS world?
Thanks for reading my story 🙂