Dimension - Repetive values

[Former Member]

If I am viewing a fact table (25 Million+ records) from SE11 is there anyway that I can easily check how often a value for one of the dimensions (columns) repeats itself.

If there is another method to do the same could you advise. Thanks

[Former Member · Accepted solution]

Hi Harpal,

Yes thats one method.

The other being to directly access the Dimension table, give your selection condition and get the no. of records.

Hope this helps.

Bye

Dinesh

[Former Member · Accepted solution]

Thanks Guys, I think my question was not that clear. I do not have any search criteria what I am trying to do is determine whether any of the values in a column appears more than 100 times. The problem being the table has in excess of 25 Million records

[Former Member · Accepted solution]

You may use a SQL code (probably native sql, it is likely open SQL doesn't support this formation) to get this.

<i>select * from DIMTAB A

where 100 <= ( select count(*) from DIMTAB b

where b.dimid = a.dimid ). </i>

you can change 100 to other values. This will work for unique combinations of columns (if there are multiple in the dimension).

You can play around with this to get top n occurances (highest occurance dimension values) list.

cheers,

[Former Member · Accepted solution]

Thanks, can I actually run a select statement from the selection screen which is part of SE11 or do I need to write a routine?

[Former Member · Accepted solution]

you can do this by writing an ABAP code (in SE38). The actual code may look like :

REPORT  ZT1.

PARAMETERS : W_COUNT type i default 100.

tables : DIMTAB.


data : dbcur(10) type c.
data : w_dimtab like dimtab occurs 1 with header line.

exec sql.
open dbcur for
select * from DIMTAB a
where :w_count <= ( select count(*) from DIMTAB b
where b.dimid = a.dimid )

endexec.

do.
  exec sql.
  fetch next dbcur into structure :DIMTAB.
  endexec.
  if sy-subrc <> 0.
     exit.
  else.
     append DIMTAB to w_DIMTAB.
  endif.
enddo.

*exec sql.
*close dbcur.
*endexec.
*

sort w_dimtab.
delete adjacent duplicates from w_DIMTAB comparing DIMID.

loop at w_DIMTAB.
write 😕 w_DIMTAB-DIMID.
endloop.

[Former Member · Accepted solution]

Thank you Ajay

[Former Member]

Hi Harpal,

Use the Dimension table directly. It would not have that many records.

Regards

Dinesh

[Former Member]

Bu the dimension table will not have the number of associated fact table records to it.

However, the dimension table can be used to get the selection values which in turn can be used on the fact table.

An easier way would be then to run a select statement directly on the fact table in the DB. Something like Select count(*) from... group by <DIM ID Field>

[Former Member]

Hi Dinesh,

Thanks for that, but this will not solve the problem since in this case the value will be unique in each case. What I need is for example:

Fact Table has a Dim8. This Dim has values are any of those values repeating themselves over 100 times?

Thanks for your time

[Former Member]

Harpal,

Give the DIM ID as a selection condition and press the number of records button. That will tell you the number of times the DIM ID is repeating in the fact table.

Cheers

Aneesh