RE: Handling A Changing Status/State Reporting

Hi,
To let me better understand your issue, could you please first answer me:
1. What did you expect to get from the OLAP cube?
2. Do you really have real-time OLAP requirements, but not a historical
data view?

Before you design a fact table, you should know that an OLAP database is at
the upper level of one or many OLTP database. The data, including
aggregated data, in an OLAP database is extracted and calculated from OLTP
databases. I agree that you create a Time Dimension for your cube. If you
want to add more date columns in your fact table, such as ReceivedDate,
ResolvedDate etc, you can also relate them the Time Dimension with foreign
keys. Also I recommend that you add some aggregated measures to your fact
table.

Regarding your concerns, please see the answers as following:
Q: "Then I need some calculated measures/dimensions(?) that are calculated
relative to the last day of any periods displayed in the pivot table. "
A: I think that you need to add some calculated measures in your fact
table.

Q:"At this point I don't know how to figure out the last day of each period
shown in a pivot table"
A: You may consider creating a calculated member for your cube. After that,
you can select the calculated member in your pivot table.

Q: "and then how to select the appropriate Status or sum or WaitingDays or
sum of ActiveDays up to that point"
A: Why do not you directly save WaitingDays in your fact table? In other
words, you have persisted the aggregated values for WaitingDays and
ActiveDays in your fact table.

Q: "Another problem with this approach is that I will have 100's of
thousands of Support Tickets. I know it's typical to have many records in
a Fact Table, but this seems like it
will produce a lot."
A: You can create multiple partitions for improving performance.

I recommend that you refer to the following articles:
OLAP Design Best Practices for Analysis Services 2005
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssas200
5.mspx
Strategies for Partitioning Relational Data Warehouses in Microsoft SQL
Server
http://www.microsoft.com/technet/prodtechnol/sql/2005/spdw.mspx

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================











.

Relevant Pages

  • Re: Problem when using DateDiff and slicing
    ... do not create a new cube or a new measure group. ... the dimensions employee and organization on the Dimension Usage pane. ... Microsoft Online Community Support ... or a Microsoft Support Engineer within 1 business day is acceptable. ...
    (microsoft.public.sqlserver.olap)
  • RE: Problem when using DateDiff and slicing
    ... Apparently the iteration should happen on Employee level, ... do not create a new cube or a new measure group. ... Microsoft Online Community Support ... or a Microsoft Support Engineer within 1 business day is acceptable. ...
    (microsoft.public.sqlserver.olap)
  • Re: OLAP cube build stuck (Project 2003 Server)
    ... It looks like the OLAP build just died. ... I'm thinking a Project Server ... Concerning the admin permissions needed to build the cube: ...
    (microsoft.public.project.pro_and_server)
  • Re: Requesting Advice on OLAP Reporting Tools
    ... that some of my current projects might benefit from the right OLAP ... I just want an app that focuses on slicing and dicing a cube, ... Out strengths are in budgeting and forecasting, management reporting, ... an application that works with any data source. ...
    (comp.databases.olap)
  • Re: OLAP cube build stuck (Project 2003 Server)
    ... cube will probably be enough - if not, ... Have you migrated the OLAP metadata repository to an sqlserver database ... identity used for communicating with the OLAP server a member of the OLAP ... I have installed SQL AS, ...
    (microsoft.public.project.pro_and_server)