It's been a year now that me and my blog have been together. We gave it our first go on May 15th, 2007. And I have to admit it's been a relationship that had its ups and downs.
At first, I wasn't completely sure how to write - what tone to use, how to approach a subject. But I guess blogging grows on you, and you grow with it, and I feel more confident now when I open a blank page and start to write. Also, at times, writing can feel like a chore. Because you're busy, and you have things to do, (but then again, when don't you?), and you just don't have the time needed to write a post that you feel would be worthwhile. You'd like something that would represent you and your subject in a serious manner, but you just don't have the time. On other occasions, you have the time, but you just don't feel like you have something to say (which is even worse than having something to say, but no time to write it down!). In any case, it makes you feel like your blog is looking back at you with a look that says something between "feed me!" or "you don't pay attention to me like you used to..." and you're left torn.
My reasons for writing have also varied over time. At first I would write just so I could remember stuff. But then, I would sometimes write just so I could mark an occasion, or perhaps even just bring a discussion that was going on in my head onto the page. In other times, I wanted to let people out there make a better based decision than I was able to.
The blog has been good to me. It helped me meet new people (something which I love), and at times even feel like I've helped (also very gratifying). And even become a member of a new community that has pampered me. What a treat!So I'd like to thank my blog for giving me all that it has given me, thank my brother for pushing to start with all of this, thank Microsoft Israel for giving me and my blog the time and space to grow, and thank you the reader for joining me here for the journey!
If you have any subjects that interest you that I haven't covered in this blog, or have covered but you'd like to hear about them more, please drop me a line through the "Contact" button at the top of the page, or put down a comment to this post.
Thank you again,
גם אני זכיתי בהזמנה לביקור של סטיב באלמר באירוע פתיחת מרכז הפיתוח בהרצליה. הגעתי בסביבות 8:40 (כשמבקשים ממני להגיע ב 9:00, אני לוקחת את זה ברצינות :)) וראיתי את כל החבר'ה מהקהילה. היה ממש נחמד להגיד שלום ולהתעדכן במה קורה.
החלטתי שאני לא רוצה לכתוב על האירוע מנקודת דיווח עיתונאית, אלא יותר לחלוק איתכם רשמים ונושאים שהותירו בי רושם מהאירוע:
תרשו לי לפני סיום להודות למיה על השימוש בתמונות וגם להגיד תודה רבה למיכל על ההזמנה. אני מאוד מודה לך על ההזדמנות לחלוק איתכם את האירוע הזה, היה ממש מרתק ומאוד נהניתי. תודה!
Creating a Report Model based on an Analysis Cube – Pros and Cons
If you follow my blog, you probably know by now that I developed a report model based on the Analysis Services cube I already had. I even got around to updating the report model.
And then, my users started using it. And you know, whatever we do just seems so wonderful to us, until our users find all sorts of flaws with what we did… so yes, my users did open my eyes to quite a few problems that may occur when you build a report model based on a cube.
I even found myself contemplating doing the model all over again based on the relational database, but my boss decided otherwise. Still, he did ask me, "So Ella, if it has all these flaws, why did you do it in the first place?" The thing is, that though I saw all sort of information out there about how problematic it may be to build a report model based on a cube and all the preparation you need to do in the SSAS project, I never remember seeing a sort of warning about all the pros and cons of using a report model based on a cube. So I thought perhaps I would make a post out of it, especially now, that I also had the fortune of building a report model based on the relational DB in another project.
So here are the main points in my view:
1. Maintenance - when you build a report model based on a relational database and you also have a cube, you will have to manage two projects. I think that this is really the main point. Because you'll have 2 DS and 2 DSV to update and you'll have to manage roles and security in 2 projects and so on and so forth… and I think that it should be quite a big consideration as to how much you want to put into maintenance of your projects. In fact, I think you'll find it quite important that they stay just as low maintenance as possible. And creating a report model based on a relational DB may cause you more maintenance than with a report model generated from your Analysis Services cube.
As long as you don't update the model you generated from the cube, any changes made to your SSAS project will show in your model, without you having to define them from scratch in the both projects, just regenerating your model after you defined the changes in your cube.
2. MDX – Analysis Services enables you to define MDX scripts and their result is immediately mirrored in your report model. If the calculations you defined are important to you, you should consider the effect of not having them if you build a model without those manipulations on your data.
3. Filtering on measures – one of the rare references I saw about problems in using a report model based on a cube was in this post, which I stumbled upon while looking for something else… as Bob Meyers points out there, "filtering on totals when using RB over AS will rarely result in acceptable performance" and believe me – it's bold in the original post for a reason! :)…
At times, the query with the filter would run a very long time, at other times it wouldn't give any rows back, even though I know they exist as I saw them without the filter on.
When I would use the filter on a measure that would have an MDX defined on it, it would give me the correct rows and in reasonable time, but without the column of the measure I was filtering on. I ran in the profiler the MDX query that the Report Builder defined for my report, and found in gave me my column with an "Error" for a value in each cell. I'm assuming that maintaining the value for the MDX calculation and filtering on it was too much to manage for report builder.
Even more so, I also have measures in certain measure group that are restricted for quite a few of my users. When a user who was blocked from one of those measures tried to use a filter on a measure in that measure group, she got an error. A user who wasn't blocked was running the same report and got the all the rows correctly (only without the measure). I was left then to believe that the MDX query that the Report Builder built for my first user failed because the extra filter he had to add to the query (a filter required because of her security definitions) made the query timeout and fail.
4. Looking at different measure groups at once – a report model based on an SSAS project will only show you measures from one measure group each time. With a report model based on a relational DB, you can show measures from several fact tables at once.
This turned out to be very critical for me as I couldn't look at my measures and compare, for instance, how my use of the budget stood in comparison to the budget itself at a certain year on the budget hierarchy. Something that seems so simple on my Panorama Nova View viewer.
The only exception to seeing measures from different measure groups in a report model based on a cube, is when your measure group is coming from a table that serves as a Fact table and a Dimension table. That means that in the “Dimension Usage” tab of the cube, you’ll see a relationship of “Fact” between the Dimension and that Fact. As there is a dual use of the table in the cube, you may see measures from different measure groups set beside one another.
5. Defining a calculated member – the MDX run by report builder for a report model based on a cube is pretty simplistic, and so it does not allow you to define a calculated member based on a dimension attribute. You can only define calculated members based on your measures. This meant for instance, that if I had an attribute in my dimension and I wanted to define a new attribute in my model, based on the first letter of my attribute, I couldn't do it in the report builder.
In a report model based on a relational DB, no such limitations exist.
Now, you could argue that all you have to do is to define your new attribute in the cube itself and then show it in your model. But then, you have to understand that you would need to add attributes to your cube according to each and every request of your users. That can bring you to a lot of attributes… and that's instead of letting each and every user define it for himself for in his own reports.
Even more so, if you have updated the report model you defined on a cube, any changes made in the cube, you will have to redo in your model. For more detail, please see paragraph number 1 and the linked post.
6. Response time – OLAP gives you predefined aggregations. As a result of that, you can promise yourself a quick response time for (most) of your queries in a report model based on an Analysis Services cube. To get the same quick response time in a report model that's based on a relational DB you need to define indexes on your tables.
So yes, defining a report model on an Analysis Services cube does have its perks, especially when you have a lot of tables in your DSV and you've used translation and MDX. But at times, the lessened usability of a report model based on an SSAS cube in comparison to one based on a relational DB, can make you reconsider. I highly recommend that you consider wisely before choosing to develop your Report Model one way instead of the other.
I recently finished defining and refining a report model based on a relational database. At first, I just set up my DSV and then added a report model with the “Autogenerate” option. Then came the grueling task of refining the report model that was generated…
I was using the MSDN pages which were very helpful on that subject, but it’d be fair to say that I something to add to what they suggest for inheritance. See, I was using the inheritance property for my entity and deployed my model to the server. Only thing, when I viewed my model in the report builder, I got this infinite loop… :(
My entity would give me itself connected to itself, and I could click on it to discover the same entity over and over and over again…
Took me a few minutes to understand that my original table was probably linking to the inherited table which was linking back to my original table and so on and so forth… So, if like me you get your entity over and over again after defining the inheritance property for it, what you need to do is this:
Go to the entity which is inheriting and hide the role to the entity it’s inhering from.
Go to the entity which is inherited and hide the role to the entity it’s inherited to.
Yep, that simple! And I know you may consider just deleting the roles entirely, but my advice is to leave them and hide them.
So that’s the way to do inheritance right in your report model.