So, now I have done about 80 percent of my project time. I have gone from not knowing much all about SSAS properties and how to create cubes with dimensions to having created some smaller cubes from different relational databases and also used some transformations in SSIS though this could have been done at some greater extent. Also I have begun to see the potential in SSRS and the depth of it. One could build really great reports with lots of dynamic stuff. As a reference I use Qlickview which is a complete different thing in many ways but I think it´s a great goal to try doing stuff that maybe was “not meant” to do. Or else SSRS was meant to be used with very advanced dynamic stuff but most people are not doing stuff like that for some reason or at least not posting them on the web. I hope I can create a tutorial where I build a big SSRS report with lots of nice features or perhaps a video.
Also there is probably more advanced stuff in SQL Server 2012 Reporting Services. Some things I have noticed that one need to have some or perhaps great knowledge in when creating these reports are:
- MDX – this is getting more and more important as I learn to do more advanced stuff in SSRS. I read somewhere that in the beginning it´s relative easy with MDX but then the steep learning curve begins, and I really feel that way to. There is so many functions and I still feel that I am thinking in some relational ways. Since you still need the knowledge of T-SQL to populate the DSV there is no way to just forget about it completely but I know that it will still take some time before I am master of MDX.
- Knowledge about how to write expressions (I found this hardest but also most rewarding since here you can do the cool dynamic stuff).
- Subreports/Drillthrough with different kinds of charts.
Some thoughts about the ERP I am working on without saying exactly which one it is:
Well, it has been out for some years now so even if my knowledge is limited I think there are lots of things that could be done better. For instance there are no aggregations what so ever. I thought this was a vital part when creating the cube.. Also there are a great of things you have to do with the parameters and names and so on in the SSRS environment to be able to see things correctly in the external report viewer. Perhaps this is the way it is with custom made ERPs?
The last 20 percent of my work here will probably go to report building and make corrections and add more stuff to the cube. By the way, I am using the iterative approach on my solution and I guess it´s the way to go but sure there are stuff that always gets messy, e.g you always need to go back into the data source view to check where the data is coming from and if and where you should add your new dimensions. And in the report phase it´s easy to get to many reports instead of a few great ones but still it´s the first time I am doing this. I realise that next time it will take more planning before starting the project. But then I will also know a lot more about the different stages in the development cycle.
The big things to focus on now are the maintenance and logging. This is a big part of the exam (70-448) and so are the SSIS which I have not worked on enough I think.