Re: CubeMeisters please help.

Hello Bob,

I will try and help. It's a bit hard to see the problem without more
complete sample data. From what you have posted I think you have the
following issue.

For any given batch you have a number of failures and an overall batch
size. You would like to measure the number of batch fail types vs. the
total batch size.

Batch Failure Type
1 Fail Type A
1 Fail Type B
1 Fail Type B
1 Success
2 Fail Type B
2 Fail Type B
2 Success

The main issue is Batch size is at a different grain to the Failure
types.

One solution is to create an aggregated count at Failure type this
would allow you to compare the types of failure against the total Batch
(is this what you are after?).

Once you have the counts by batch type you are able to create the
following sample set of data.

Eg.

Batch Fail Type A Fail Type B Success Batch Size
1 1 2 1 4
2 0 2 1 3

>>From this you would be able to calculate the percentage of failure
types against a total batch size in a cube or report.

This would best be done in a fact table of course. You can still have
the following dims at this level. Batch Size (a band dimension), Time,
location, etc.

A band dimension is a range dim allowing you to group up batches into
sizes.

To create this you would need a count for each batch type say using a
UNION Statement with a group by to bring the results to the batch size
level. Have a look at my Hackie Hack code below.

Eg.

SELECT
FB.Batch
, SUM(FB.FailTypeA) AS FailTypeA
, SUM(FB.FailTypeB) AS FailTypeB
, SUM(FB.Success) AS Success
, SUM (FB.BatchSize) AS BatchSize
FROM
(
SELECT
Batch
, Count(*) AS FailTypeA
, 0 AS FailTypeB
, 0 AS Success
, 0 AS BatchSize
FROM BatchLog
WHERE BatchType = 'Fail Type A'

UNION ALL

SELECT
Batch
, 0 AS FailTypeA
, Count(*) AS FailTypeB
, 0 AS Success
, 0 AS BatchSize

FROM BatchLog
WHERE BatchType = 'Fail Type B'

UNION ALL

SELECT
Batch
, 0 AS FailTypeA
, 0 AS FailTypeB
, Count(*) AS Success
, 0 AS BatchSize
FROM BatchLog
WHERE BatchType = 'Success'

UNION ALL

SELECT
Batch
, 0 AS FailTypeA
, 0 AS FailTypeB
, 0 AS Success
, Count(*) AS BatchSize
FROM BatchLog ) AS FB

GROUP BY FB.Batch

I hope this helps if not, I would try and re-post this to the OLAP news
group.


Myles Matheson
Data Warehouse Architect

.

Relevant Pages

  • Re: CubeMeisters please help.
    ... The cube contains aggregated failure data which can be sliced and diced down ... to batch level. ... > 1 Fail Type B ... >, SUM(FB.FailTypeA) AS FailTypeA ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Hi, what in your opinion is best quality DVD blank discs out there?
    ... batch to batch within the same brand. ... While even Taiyo Yuden will have an occasional coaster, the success ... same stock is still on the shelf). ... "The reasonable man adapts himself to the world; ...
    (alt.video.dvd)
  • Batch printing calendar events with notes
    ... Anyone had success in printing a series of events and notes from ... different time periods as a batch? ...
    (microsoft.public.mac.office.entourage)
  • Dolphins vs. Steelers
    ... Culpepper wants to prove that it wasn't all Randy Moss.... ... But hard to see Batch having any success ... Batch to do something no one on Pitt sideline wants to see: ...
    (rec.sport.football.college)
Loading