July 10, 2009 at 2:30 pm
Hi there,
I am in bit of a tight spot.. I am trying to sell Report Builder 2.0 as the best expert end user tool.. but the client has complex filter requirements which I fear are out of RS 2.0 league:
The customer would need to be able to answer the following question using the Quesry Designer (example):
- (All customers who have bought a car in 2006 for the first time)1 and (have bought a second car at least one year later.)2
Using TSQL you would be able to answer the question. There is an dependancy between the year from result set 1 (2006).. and resultset 2 (at least one year later) which is 12 months from the buy date.
So basicly resultset 2 is dependent on result 1
Please, does anybody know how you would realize that in RS 2.0 using the User Interface (using the Group Filter func. etc)?
Thanks in advance
July 11, 2009 at 8:41 am
Instead of trying to setup filters this would be more optimal to setup in the cube to return your results. This is exactly the types of questions that SSAS was designed for to be able to return the answers for. You would want to create a named set in your cube to return these values and you could set this up based on any year selection by referencing the CurrentMember.
I can't find an example with your specific requirements right now, but you can get a general idea of how to do this by looking at this example and doing some MDX modifications to incorporate your requirements to get at the results you are looking for.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 11, 2009 at 8:46 am
While Report Builder 2.0 is a great front-end reporting tool for end-users, you might need to explore other tools that will allow end users to create these types of reports and be able to leverage SSAS. ProClarity allowed users to be able to create reports like what you are looking for fairly easily without the end user having to know a lot of MDX, but when using SSAS as a source MDX knowledge is always a plus.
Take a look at this list to get some other options.
http://ssas-info.com/analysis-services-client-tools-frontend
Having a good understanding of MDX will always be an advantage when trying to answer questions like you are trying to.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 11, 2009 at 10:37 am
Thank you for your reply.
Ok my understanding of MDX is novice, I didnt now you could make named set dynamic.. thought it was static. But If I would be able to make a named set and generate a report model based on that cube, would the end user be able to make use of that named set through Report Builder 2.0 ?
Thing is that report model offers many other plus points one strong one being letting the user use OR and AND logical statements.
July 11, 2009 at 10:41 am
The client in question has a dynamic set of questions which change often. Using named sets would mean that for each new question you would have to make a named set.
July 11, 2009 at 3:23 pm
That is why requirements gathering is extremely critical in the creation of any solution. Without the proper requirements how can you just try and build a solution unless you know what it is going to be used for.
I would suggest going directly against the SSAS cube instead of using a report model for an SSAS data source. With Report Builder 2.0 you no longer are restricted to just using Report Models. Going agains the cube will provide more flexibility and access. Plus you won't have to rebuild the model each time.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 12, 2009 at 5:46 am
Understood, the core business of the potential client is to be able to answer questions which he receives from his clients (scientists). With named sets you would have to configure the cube each time the clients has to answer a new 'undefined' question.
That is why I didnt consider the Excel 2007 - Cube and RB 2.0 - Cube options.
July 12, 2009 at 6:57 am
With Excel 2007 and RB 2.0 going directly against the cube that would allow for a little more flexibilty with the end-users since they could define their own calculations and named sets to get at the results that they are looking for (assuming that they have some understanding of MDX).
With Excel 2007 there is a nice add-in on CodePlex - http://olappivottableextend.codeplex.com/[/url]
With RB 2.0 you can do this in the Analysis Service designer built-in to the tool.
Granted this does decentralize the logic, but it does allow a little more flexibilty then using a Report Model and the Report Model support for SSAS is definitely not the greatest. Eventually you would want to push the logic that end-users are creating back intot he cube so that you have it centralized.
Maybe the use of Gemini with SQL Server 2008 R2 will allow users more flexibility.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 12, 2009 at 9:28 am
Thank you for digging with me through the possibilities!
My potential client does not know the first thing about MDX or TSQL. The SSAS designer in RB 2.0 is centralized (excluding the option of named sets) arround a single dataset. The client, however, wants to be able to exclude or include data based on conditions crossing numerous datasets e.g 'Give me all customers who are female and bought a car in 2006 and a second car minmum a year later (365 days from first purchase date)'. That last date contraint is between two datasets, one with all cars purchased in 2006 and the second with females having bought a second car.
In MDX or TSQL its absolutely doable, but for an business user, using the UI (which is the main requirement), it is difficult if not impossible. Going back to the example I have given earlier; through parameters in RB 2.0 it is possible to exclude or include data in one dataset bij filling the parameter with data from another but you wil not be able to exclude data based on the mentioned date constraint.
However, the report model - RB 2.0 is the only combination, that I found, which provides the business user with the possiblilities of advanced contraints using logical operators and groups. With the SSAS designer in RB 2.0 (or Excel 2007) the business user can not apply logical groups of conditions or exclude data based on a second, third or more dataset.
Ill try to get into contact with a Microsoft BI specialist tomorrow @ work to see if future releases of RB 2.0 and / or GEmini will have these features.
In the mean time I am afraid ill have to answer the clients question with 'no not completely, but why not learn mdx' 😉
July 15, 2009 at 1:06 am
I have had contact with Microsoft and Gemini will not support features which will enable the users to create time-based depencies between datasets. Not even ProClarity 6.3 supports this analytics-adhoc reporting scenario.
I took your advice Dan and adviced the client to incorporate complex questions in the data warehouse (or cube).
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply