Problem with the results of a report

[AnaFlor]

Hello,

I have one report with a metric that sums a value and that have the attribute month.

In the fact table I have the code of the month and I have just one table with all the dates (year, month, day, etc.)

The problem is that the result should be 1 but it gives me 31, 1 for each day of the month (in the case July).

How can I solve this problem withouth creating views (one for each attribute)?

My RDBMS is sql server 2000.

Thanks.

 

[entaroadun]

What is the value that you are summing? What field is it and which table is it attached to? Please be more specific if you want help.

 

[AnaFlor]

My fact table has the following fields:
- Month, Client and value of payment.

I want a report with month state(that is relationed with client and the value of payments.

The problem is that because I have my dimension time in only one table it is giving me the value*the number of days of the month

Can you help me?

 

[entaroadun]

No way to do it without a view on your time table. Sorry.

I'm sure there's a technote out there that talks about this, but basically, if you have a fact table at a particular level (i.e. Month), then you need to have a lookup table at that same level. If you have a lookup table at a lower level (i.e. Day), then you will get that multiplier effect.

MSTR has always been this way, and I don't think they will fix it because the issue is well known. Sorry.

 

[jonhoward]

What if you were to use the fact table as the lookup for Month?

TN4000-7X0-0833 has some extra info

 

[mas09679]

I cannot use that fact table as a lookup for month because it's not my only fact table with month and it cause some problems, right?

I was trying to create a view like this:
create view v_year as select distinct cod_year, desc_year from dim_time

now I was trying to create a trigger for, whenever my dim_time is updated, deleted or inserted my v_year would be updated

I was trying to do like this:
create trigger t_year on dim_time
after update, insert, delete
as
drop view v_year
create view v_year as select distinct cod_year, desc_year from dim_time
go

but is not allowing me to create a view in the create trigger statement

How can I solve this?

Because I wanted my view to be updated automaticly.

Thanks

 

[AnaFlor]

The previous question is mine.

I wroted it in a borrowed PC.

Thanks

 

[Z3]

Don't bother with the trigger. Your view will updates automatically anyway - it's a view, not a table.

I still don't know what are you trying to do. It'd be easier if you gives all tables and desired SQL.

 

[AnaFlor]

No problem.

With the views I solve my problem.

Thanks