July 31, 2014 at 5:39 am
I am currently working on a project building a new IT system, and as part of this the supplier is providing a management information system based on SSAS, cubes and MS Report Builder V3. All fine and in line with the tender, however I am not convinced that cubes will provide the best solution and am looking for any advice or ideas to improve things - I am happy to educate around BI but this is a challenge where staff already believe they know everything there is around data analysis. More details below.
The current solution:
We have a cloud based database for 7 organisations, with 7 modules each recording around 100,000 records per year per organisation. These cover details of events, people and locations, along with other related objects.
From this there are currently 8 OLAP cubes, with an average of ~120 dimensions per cube. Dimensions often these dimensions have missing data (legitimately, not a data quality issue) which will drop out rows of data due to no logical joins. These are queried via Report Builder (v3) with the reports available via SSRS.
The challenge:
The only real business requirement I can get from the end users (from 7 separate organisations) is that they need to be able to answer any possible question from the data as they do not know what will be asked. In reality there are constraints to this but they all state that they can access every bit of data under their current set up and this should not change.
Linked to this is the fact that there is no experience in business intelligence, but focus around reading text and making connections. Exceptionally limited skills are present beyond Excel and we can not simply replace all the staff so need to take them along with the solution. Most are used to relatively flat data which is exported into Excel and dealt with from there.
The Requirements:
In essence we need to be able to quickly and easily produce reports which count the data and reports which can be used to extract data for use in other tools. We need to do this without technical involvement in terms of writing specific SQL etc.
I know that this is a bit vague but I have had to try and get up to speed on OLAP/MDX myself as I come from the same background as the end users and not from a BI experience.
Thanks for any ideas or help....
July 31, 2014 at 6:58 am
A cube is the way to go if there is no technical expertise and you need to answer as much questions as possible.
120 dimensions for a cube is a lot. Aren't there dimensions that can be merged together.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 31, 2014 at 7:12 am
Not sure around merging dimensions - can you expand on this at all? I guess you mean two dimensions, X containing X1, X2, X3, and Y containing Y1, Y2, Y3, could be merged to a single dimension of XY containing X1Y1, X1Y2, X1Y3 and so on to 9 possible members?
This may be something to look at, although quite a few have significant numbers of members which could stack heavily.
But something for me to think on - thanks,
July 31, 2014 at 7:18 am
S_francis_uk (7/31/2014)
Not sure around merging dimensions - can you expand on this at all? I guess you mean two dimensions, X containing X1, X2, X3, and Y containing Y1, Y2, Y3, could be merged to a single dimension of XY containing X1Y1, X1Y2, X1Y3 and so on to 9 possible members?This may be something to look at, although quite a few have significant numbers of members which could stack heavily.
But something for me to think on - thanks,
Something like that yes. For example product dim, product category dim which could be merged into one bigger product dim.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply