SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Rethinking Analysis Services


Rethinking Analysis Services

Author
Message
DavidP-340734
DavidP-340734
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 105
Comments posted to this topic are about the item Rethinking Analysis Services
Leo Cohn
Leo Cohn
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 77
Why did you not consider using the Office Web Components?

OWC Pivot tables would enable you to do the OLAP reports without building another OLAP database.

Using OWC, Fig 6 would be one pivot table with 1 chart that the users could manipulate themselves.

OWC would provide user interactivity for any report or chart

Did you want a server-side only solution? Or because OWC is "deprecated" on Office 2007?

I am writing because I use OWC extensively with both OLTP databases and DW designs.

Curious,

Leo
Bertil Forsberg
Bertil Forsberg
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 161
Hi,

I don't know that much about OWC but I really enjoyed this article! Especially as I'm in the middle of learning the BI concept as a whole. It's a great example of how you can tie SSAS and SSRS(and also SQL 2005 + ASP.NET) together in a realworld scenario. The SSIS(here 'replaced' by user input through a web page) part of the BI concept is a bit more straight forward/easy to learn though quite improved from dear old DTS/SQL Server 2000.

Well done David!
/Bertil F.
Martin Vrieze
Martin Vrieze
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 125
I'm going to play devil's advocate....

How much total development man-hours was spent creating this infrastructure, the web interface and all of the reporting?

For $2000-$3000 this can be outsourced to one of several ASP's who's toolsets allow for any type of survey question format imaginable. In return, they pass back a complete dataset that can be imported/analyzed using any off-the-shelf statistics package.

This is not a question of "Can we do this?". Of course we can.

It is a question of "Should we do this?" I argue that there are several outsource vendors that can get this done much more cost effectively.
Tahir-191992
Tahir-191992
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 84
Dear Martin Vrieze
It is not the matter of man-hours and cost or solution to SURVERY task but is a creative way of using some technology and offcourse my answer is YES to your question "Should we do this?" if we are working in some department and being paid monthly.


Kindest Regards,

Tahir

R Wong
R Wong
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 49
The author mentioned that one of the reporting needs was to display real-time results. Also, the author mentioned that this request was dropped on his desk, Friday afternoon. Under such circumstances, I am not too sure outsourcing will be as cost effective as what the author had done(over the weekend may be?)
mark.frawley
mark.frawley
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 68
Hope you got a nice bonus for pulling this off so fast (but working for the state, I doubt it).

There is another way of structuring the fact table which would be much more flexible in the event questions were added to the survey, or the possible answers became more than just 3, or what the possible answers were varied by question: introduce two new dimensions, a Question dimension with one member per question, and an Answers dimension with one member per possible answer (across all questions). Create foreign key relationships between these and the fact table. Then, instead of one measure column in the fact table per question/answer combination, just have a single numeric measure column whose value is always 1 for each row. This is possible because each row in the fact table represents a particular survey #/Question/Answer combination. Non-existent combinations don't exist in the fact table (or if they did, then the value should be zero and they wouldn't hurt anything). Then you can just aggregate this measure (no counting needed) and still get all slices desired. To replicate the query result shown in the article with question/answer and count would now require placing both Question and Answer dimensions on the rows.

Of course, the UI for entering the results and the underlying relational schema would need corresponding adjustments also were any of these changes in question/answer possibilities to occur.
M Smith-673194
M Smith-673194
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 17
I glad to see that I'm not the only one who wakes up at 3:00am with solutions. :-)
Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 1290
A great article in my opinion....would be amazing if you have any more of these experiences
Jonathan Mallia
Jonathan Mallia
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1231 Visits: 1290
A great article in my opinion....would be amazing if you have any more of these experiences
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search