All,

I have a problem with designing a dimension. I have a star schema and
the dimension in question is linking to the fact table through
surrogate key.

tblItem ==> table in dimension
Id -----> join to fact table
Code
Description
MTM
Accrual
LastUpdated

My problem is designing another dimension based on MTM and Accrual
fields in this table (both bit fields) . I want the new dimension like
below (only one level with two members)

Type (one level)
-MTM
-Accrual

Any idea how I can acheive this.

Thanks,
Arun

Assuming the dimension table is actually a view (which I hear is good
practice), you could add a field like this:

Type = case
when MTM = 1 and Accrual = 0 then 'MTM'
when MTM = 0 and Accrual = 1 then 'Accrual'
else 'UNDEFINED'
end

This assumes MTM and Accrual are never set to 1 for the same record but
you could build on this if the relationship between both fields is more
complex.

Designing your desired dimension should then be straightforward enough
using your new 'Type' field.

Cheers,

SR

The problem all the below possibilities are valid

MTM Accrual
1 0
0 1
1 1

When I select 'MTM' in the new dimension, then all records with MTM = 1
should be displayed irrespective of Accrual and vice versa.

Cheers,
Arun

Now I haven't tested this but maybe you could give it a shot:

1. Insert a dummy foreign key in the view pointing to your fact table
(set it to 1)
2. Use this field as the top level of a new dimension in your cube
3. Create two calculated members below that new dimension along the
lines of:
a) iif([yourdimension.MTM.name] = 1 then 'MTM') and
b) iif([yourdimension.Actual.name] = 1 then 'Actual')

Not sure if this makes sense. Let me know what you think...

SR

Thanks for that. I will give it a try. I had another idea.

1. New table
tblNewDimension
Id -- Dummy
Type
MTM
Accrual
2. Populate with following values
Type MTM Accrual
MTM 1 0
MTM 1 1
Accrual 0 1
Accrual 1 1
3. Now I create a new dimension with this table. Level ==> Type column
4. In cube editor, I link this table to existing dimension table,
through two columns MTM and Accrual.


I think this will also work. My only question now is, Is this correct?

Hi everyone.

I'm currently writing on my diploma thesis and currently facing some
(hopefully minor) problems when I try to implement a datacube. I really
hope someone from this list can point me to some useful information.

Anyway, I'll now give a short overview of my problem. I shall implement
the results from a national survey to a datacube, so far no problem
since I've already done tasks like this before. I've written a
sql-script that sets up the datacube with point estimates for all the
cells of the datacube.

I should however, provide additional information to the values in the
cells of the datacube. From a statistical view, I should provide a
variance estimate within the concept of the data-cube. I think that I
may archieve this goal by defining a computed measure.

The difficult part now is that I should somehow (and I really don't have
any clue how I could solve this problem) provide a link between the
additional information of the computed measure and the actual value from
the factor table in a selected frontend (microsoft excel). I should do
this by a conditional format my supervisor told me.

Let me give an example. In a certain cell of the data-cube the
corresponding value of the datacube my be 100. The variance estimate
(from the computed measure) may be 10. When I use the data cube as a
data source in ms-excel, the cell where the point estimate (100) occurs
should be formated differently conditional on the value of the variance
estimate. If the Variance estimate is < 10 the background of the cell
should be green for example, if the variance estimate is beetween 10 and
100 the background of the cell should be orange and if it is lager than
100, the background should be red. I hope it is clear what my problem is.

Has anyone already done a similar task and may give me some information
howto start working on this problem. I'd be very, very glad if someone
has a hint for me

Greetings,
Bernhard

Ps: Sorry for my bad english...

Refer to this March 2001 SQL Server magazine article by Russ Whitney,
entitled: "Setting Display Characteristics". It discusses both
server-side and client-side techniques for conditionally controlling
cell color:

http://www.sqlmag.com/Articles*/Inde...rticleID=16514


Excel alone may not support server-side formatting. But Office Web
Component (OWC) Pivot Tables support it:

http://support.microsoft.com/default...b;en-us;318122
PivotTable Component

SUMMARY

SUMMARY

The Microsoft Office XP PivotTable component supports two undocumented
properties: UseProviderFormatting and DisplayCellColor. These properties
allow you to use server formatting for cells and totals in a PivotTable
view.

• When you set the UseProviderFormatting property to True, the
PivotTable component applies number formats that are stored on the
server to the totals that appear in the PivotTable view. The default
setting for this property is False.

• When you set the DisplayCellColor property to True, the PivotTable
component applies Forecolor and Backcolor settings that are stored on
the server to the cells that appear in the PivotTable view. The default
setting for this property is False.

• The PivotTable component does not support other format properties that
can be set on the server, such as FontName, FontSize, and FontFlags.

NOTE: The UseProviderFormatting and DisplayCellColor properties are
hidden members in the Microsoft Office XP Web Components object model.
These members are not documented and, therefore, these members are not
supported by Microsoft Technical Support. The sample code in this
article is provided "as-is."
...

There are Excel OLAP Add-Ins, including from Microsoft:

http://www.microsoft.com/office/solu...eladdin/overvi
ew.mspx

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***