There is OTP data for which an overall OLAP %value is required.
i.e. number of say bad eggs over total number of eggs gives performance of
farm.
Problem is that because there are many types of egg failures there is an OTP
data row for each failed egg
Something like :
Batch FailType
1 FailA
1 FailB
1 FailA
2 FailA
3 FailB
I have no trouble aggregrating the fails and using Batches as dimensions.
The problem comes when you try to bring the batch size into the cube.
The batch sizes are available from the OTP but I can't see how to deploy
them.
If I simply add the batch sizes to the end of the data row then they will
not sum correctly eg batch 1 in the example would a batch size 3 times
greater than the correct value because there are three data rows. Batches
two and three would actually give correct batch sizes because there is only
one data row to be summed.
So I thought that if I introduced the reciprocal of the batch size to the
data row then that would sum correctly because each row represents 1/ batch
size eggs.
So the maths to get batch size from the cube would be :
sum of bad eggs / sum of reciprocals.
Great on paper (I think) but when I run it real world it is only close. eg
expecting one batch with 14 bad eggs and reciprocal sum of .0940542886 to
give a batch total of 115 but it gives 148.
It seems to me that this is such a general problem that someone must have an
answer.
Am I wrong in thinking that the batch size has to be a measure? Can it be
brought into play in another way.
My real world cube is dimensioned by time, area->batch, failtype
So the cube answers questions like:
1) the total failures for 2004 Q4 was N
2) The number of FailtypeA in area X in 2004 was Y etc etc.
3) Batch 988 had 3 failures in total in 2004 Q4
What I need to do is relate these absolute values to the batch size at that
dimension level.
eg Q1 becomes the overall % failure for 2004 was n% which is the total sum
of failures in 2004 divided by the sum of the batch counts in 2004.
I would appreciate any pointers into solving this.
Thanks
BobHello 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|||Hello Myles,
Thanks for your reply.
Your understanding of the problem is correct.
The cube contains aggregated failure data which can be sliced and diced down
to batch level.
To carry on the poultry analogy, its primary function is to compare failure
rates between areas then drill down to farm and batch level where there
appears to be an abnormality.
If I understand correctly you are suggesting a cross tabulation in the OTP
dataView.
This will ensure one line of data per batch with a batch count at the end.
In the cube I could then use a calculated member to add all of the
individual failure codes aggretations together to give the total absolute
failure per batch.
This can then be divided by the batch size to give the failure ratio.
Sounds good anyway
I will give this a go.
You mention an OLAP news group. I thought this was the OLAP newsgroup.
Would you have the URL for the news group?
regards
Bob
<Myles.Matheson@.gmail.com> wrote in message
news:1118039908.273890.58340@.o13g2000cwo.googlegroups.com...
> 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
>
> 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
>|||Hello Bob,
That's correct creating a view or a star schema with the counts of
fail types should give the answer you are after. I have just solved
this problem on project we are currently doing.
There is an OLAP news group for Analysis services and OLAP cube design.
See microsoft.public.sqlserver.olap
Its more on technical Issues with Analysis services.
Seeing you may have your answer I guess this was the correct newsgroup
;)
Myles|||Hi Myles,
Thanks.
The crosstab is coming along nicely.
One question more if I may.
There is one piece of data that is at individual 'egg' record level that I
want to drag up to batch level.
So I do a subQuery (Select top 1 blah blah).
This makes the overall query so expensive that it times out.
I am willing to accept the expense as it will only be run during the
creation of the cube and for incremental cube updates.
How can I tell SQL Server 2000 to let it run regardless of how long it
takes.?
Thanks again.
Bob
<Myles.Matheson@.gmail.com> wrote in message
news:1118106965.736548.272810@.g49g2000cwa.googlegroups.com...
> Hello Bob,
> That's correct creating a view or a star schema with the counts of
> fail types should give the answer you are after. I have just solved
> this problem on project we are currently doing.
> There is an OLAP news group for Analysis services and OLAP cube design.
> See microsoft.public.sqlserver.olap
> Its more on technical Issues with Analysis services.
> Seeing you may have your answer I guess this was the correct newsgroup
> ;)
> Myles
>|||Hi Myles,
OOPs,
It was only the sql Server enterprise manager that was timing out looking at
the view.
Cube Processing is fine.
thanks
bob
"Bob" <bob@.nowhere.com> wrote in message
news:ePx7tR7aFHA.2756@.tk2msftngp13.phx.gbl...
> Hi Myles,
> Thanks.
> The crosstab is coming along nicely.
> One question more if I may.
> There is one piece of data that is at individual 'egg' record level that I
> want to drag up to batch level.
> So I do a subQuery (Select top 1 blah blah).
> This makes the overall query so expensive that it times out.
> I am willing to accept the expense as it will only be run during the
> creation of the cube and for incremental cube updates.
> How can I tell SQL Server 2000 to let it run regardless of how long it
> takes.?
> Thanks again.
> Bob
> <Myles.Matheson@.gmail.com> wrote in message
> news:1118106965.736548.272810@.g49g2000cwa.googlegroups.com...
>|||Hello Bob,
sorry but I am not sure if I understand, what is timing out? The query
from with in DTS or query analyzer?
or the Qurey its self in your stored proc?
myles|||Hello Bob,
I use to use enterprise manager for creating the odd view too. But
mainly I use query analyzer for creating views. CREATE VIEW myView As
blah...
To fix your time out issue with EM, from the MMC there is an Option
under Tools to change the time out setting.
MMC>SQL Server Enterprise Manager >Tools> Options>Advance Tab
Hope this Helps,
Myles
No comments:
Post a Comment