Can OLAP cubes be stored on report manager aswel as reporting services reports?
Many thanks
GregNot sure what you mean. Cubes can be data sources for reporting services.
You can have a shared data connection stored in reporting services, and that
data source can be analysis services. Is that what you're asking?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
> Can OLAP cubes be stored on report manager aswel as reporting services
> reports?
> Many thanks
>
> Greg|||He might be asking about local cubes. You could store the .cub file in RS
but the server wouldn't know anything special about it. I don't think the AS
engine would be able to load it either.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u5rQ3%23F5EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Not sure what you mean. Cubes can be data sources for reporting services.
> You can have a shared data connection stored in reporting services, and
> that data source can be analysis services. Is that what you're asking?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:B38B7CC1-7390-4807-8A76-E928B2046044@.microsoft.com...
>> Can OLAP cubes be stored on report manager aswel as reporting services
>> reports?
>> Many thanks
>>
>> Greg
>
Showing posts with label olap. Show all posts
Showing posts with label olap. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
CubeMeisters please help.
My previous post drew no responses so I thought I would restate the problem.
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
Bob
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.
BatchFailure Type
1Fail Type A
1Fail Type B
1Fail Type B
1Success
2Fail Type B
2Fail Type B
2Success
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.
BatchFail Type A Fail Type B Success Batch Size
112 1 4
202 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.googlegro ups.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.googlegr oups.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.googlegr oups.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
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
Bob
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.
BatchFailure Type
1Fail Type A
1Fail Type B
1Fail Type B
1Success
2Fail Type B
2Fail Type B
2Success
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.
BatchFail Type A Fail Type B Success Batch Size
112 1 4
202 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.googlegro ups.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.googlegr oups.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.googlegr oups.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
CubeMeisters please help.
My previous post drew no responses so I thought I would restate the problem.
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
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
Cube Refresh
Hi,
I hope someone can clarify this to me.
I have DW Database which is the for a OLAP Database I
created in AS. I have created cube with the necessary fact
tables and the dimensions. my question is whenever the
Data in the DW is updated, do u need to manually refresh
the cubes so the cube would have data? is this automatic
or else can this be automated?
Thanks
Regards
ImranYes and yes, you need to "re-process" the cube when you add data to the
underlying tables. You can automate this by using the process cube task in
DTS.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <anonymous@.discussions.microsoft.com> wrote in message
news:b70e01c40d94$5e25e840$a001280a@.phx.gbl...
> Hi,
> I hope someone can clarify this to me.
> I have DW Database which is the for a OLAP Database I
> created in AS. I have created cube with the necessary fact
> tables and the dimensions. my question is whenever the
> Data in the DW is updated, do u need to manually refresh
> the cubes so the cube would have data? is this automatic
> or else can this be automated?
> Thanks
> Regards
> Imran|||Our team uses DTS to populate a special datawarehouse db and then trigger an
alysis server to reprocess it's cubes. We have this automated to run every
15 minutes. In our DTS package, we just needed to drage the SQL Server OLED
B provider, an execute SQL
task that kicks off our datawarehouse data import stored procedures, and the
n an analysis services processing task. The processing task would then just
be set up with a work flow to execute after the data import stored procedur
e. This has worked splendi
dly for us.
I hope someone can clarify this to me.
I have DW Database which is the for a OLAP Database I
created in AS. I have created cube with the necessary fact
tables and the dimensions. my question is whenever the
Data in the DW is updated, do u need to manually refresh
the cubes so the cube would have data? is this automatic
or else can this be automated?
Thanks
Regards
ImranYes and yes, you need to "re-process" the cube when you add data to the
underlying tables. You can automate this by using the process cube task in
DTS.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Imran" <anonymous@.discussions.microsoft.com> wrote in message
news:b70e01c40d94$5e25e840$a001280a@.phx.gbl...
> Hi,
> I hope someone can clarify this to me.
> I have DW Database which is the for a OLAP Database I
> created in AS. I have created cube with the necessary fact
> tables and the dimensions. my question is whenever the
> Data in the DW is updated, do u need to manually refresh
> the cubes so the cube would have data? is this automatic
> or else can this be automated?
> Thanks
> Regards
> Imran|||Our team uses DTS to populate a special datawarehouse db and then trigger an
alysis server to reprocess it's cubes. We have this automated to run every
15 minutes. In our DTS package, we just needed to drage the SQL Server OLED
B provider, an execute SQL
task that kicks off our datawarehouse data import stored procedures, and the
n an analysis services processing task. The processing task would then just
be set up with a work flow to execute after the data import stored procedur
e. This has worked splendi
dly for us.
Saturday, February 25, 2012
Cube Model
After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.
This guy has a solution:
http://codebetter.com/blogs/brendan.tompkins/archive/2007/02/28/SQL-2005-Report-Models-from-an-Analysis-Server-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.
This guy has a solution:
http://codebetter.com/blogs/brendan.tompkins/archive/2007/02/28/SQL-2005-Report-Models-from-an-Analysis-Server-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Reporting Services or SSMS - is there any way to modify the model and re-deploy? It appears there is not - as there are ways for OLTP data sources.
Cube Model
After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.This guy has a solution:
http://codebetter.com/blogs/brendan...-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8
C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.This guy has a solution:
http://codebetter.com/blogs/brendan...-OLAP-Cube.aspx
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:61D3B225-6B31-4FE3-8
C8F-D9330DB04EE9@.microsoft.com...
After generating a Cube Model (model from an OLAP source) from either Report
ing Services or SSMS - is there any way to modify the model and re-deploy?
It appears there is not - as there are ways for OLTP data sources.
cube has been updated by the server, data is now obsolete
Client issues query which sends out individual requests to the 2 OLAP servers that are load balanced. The client evaluates the versions of the returned record sets to ensure the consist data being returned for one single query. Otherwise, this error will be seen:
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.Any infomational messsage or error on SQL error log?|||I'll see if there are any other associated messages or logs and post them.
The cube has been updated by the server the data is now obsolete.
These servers sit behind a cisco 11506 CSS with load balancing based on balance type: least busy server, also persistence based on cookies.
My developer says this worked fine for a long time then just 'started happening'.
any suggestions are appreciated.Any infomational messsage or error on SQL error log?|||I'll see if there are any other associated messages or logs and post them.
Cube design e best client tool to access olap database
Hi all,
My doubt is about the best client tool to use for access cube olap. Currently i use excel for some tests but when expand until third ou fourth level the query delays a time not acceptable many times hanging the virtual server.
Another doubt is about cube design the bahavor described above is normal?
Is my server i have a fact table with 211.000 rows, 17 columns (with two calculations columns) and 15 dimension tables ( I think tha is a small database). This tables are organized in a starschema inside a SQL Server 2005 database. The cube 15 dimensions (one with two hierarquies) and with five partitions based in the hierarquies.
Virtual Server Configuration:
512MB Physical Memory.
CPU Intel Core Centrino 1.66GHz
Windows 2003 SP1
SQLServer 2005 SP2
Apologies about my english.
Thanks
Hi!
It is a little bit hard to tell without knowing more about your cube. Have you designed proper attribute relationsships and aggregations? You can download the performance guide for AS2005 pointed to a the top of this group.
Next, I think that a cube of that size and that many dimensions will require more memory than 512 MB. I also run AS2005 on my laptop but not as a virtual server(because it is slower) and I have 2 GB of physical memory.
The problem in Excel might be related to the physical limits of you machine. To do a real test of Excels limits you should run it from a workstation without any server software installed. In that case 512 MB can be enough even if most workstations today have at least 1 GB RAM installed.
HTH
Thomas Ivarsson
Friday, February 24, 2012
Cube Deploy Error
Hello, I'm trying to deploy a cube on Analysis Services and I'm getting the next error:
Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: DSAP COMBOS, Partition: DSAP COMBOS, Record: 1.
I'm sure the data is consistent and has integrity.... and I verified It ...
Could you please give me a clue ?
What do I have to post or which script do I have to send you ?
Thanks a lot !
jortiz wrote:
Hello, I'm trying to deploy a cube on Analysis Services and I'm getting the next error:
Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: DSAP COMBOS, Partition: DSAP COMBOS, Record: 1.
I'm sure the data is consistent and has integrity.... and I verified It ...
Could you please give me a clue ?
What do I have to post or which script do I have to send you ?
Thanks a lot !
Hello, Is there any service like a kind of remote desktop via Web, to show you the problem ?
|||Assuming your cube is built on top of a SQL Server relational database, your best bet might be to use Profiler to capture the SQL queries being generated by SSAS as it processes your cube. Then look at the queries and try to determine what query is returning unexpected data. More than likely the problem lies in the design of the dimension usage within the cube. Perhaps one of the dimensions is related to one of the measure groups based on the wrong attribute or fact table column?
HTH,
Dave Fackler
|||Assuming your cube is built on top of a SQL Server relational database, your best bet might be to use Profiler to capture the SQL queries being generated by SSAS as it processes your cube. Then look at the queries and try to determine what query is returning unexpected data. More than likely the problem lies in the design of the dimension usage within the cube. Perhaps one of the dimensions is related to one of the measure groups based on the wrong attribute or fact table column?
HTH,
Dave Fackler
Hi Dave, It doesn't matter which dimensions and fact table I use, I get a similar error like this:
Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_HSAP_REDENCIONES, Column: NMPRODUCTO_KEY, Value: 1. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: DSAP PRODUCTO of Dimension: DSAP PRODUCTO from Database: SAPIN_BI, Cube: Sapin BI Dllo, Measure Group: HSAP REDENCIONES, Partition: HSAP REDENCIONES, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while processing the 'HSAP REDENCIONES' partition of the 'HSAP REDENCIONES' measure group for the 'Sapin BI Dllo' cube from the SAPIN_BI database.
|||
Based on the errror message, it would appear that your fact table has a column named NMPRODUCTO_KEY and a record was read from the fact table that has a value of 1 in that column. In addition, the measure group HSAP REDENCIONES would appear to be related to the DSAP PRODUCTO dimension using the NMPRODUCTO_KEY column to match the DSAP PRODUCTO attribute (which appears to be serving as the key attribute in the dimension). The question is: do any of the members in the DSAP PRODUCTO dimension have a key value of 1 for the DSAP PRODUCTO attribute? What column in the underlying dimension table is the DSAP PRODUCTO attribute based on?
Dave F.
|||Sorry if I interfere. Have you added a new column to your fact table lately that was not in the processed cube initially?
If you use query binding for the partition(s) this new column will not appear automatically. You will have to add it yourself.
This looks like a problem that I had a few days ago.
Regards
Thomas Ivarsson
|||Sorry if I interfere. Have you added a new column to your fact table lately that was not in the processed cube initially?
If you use query binding for the partition(s) this new column will not appear automatically. You will have to add it yourself.
This looks like a problem that I had a few days ago.
Regards
Thomas Ivarsson
Thank you so much for your answers !!
|||What solution did work?
Regards
Thomas Ivarsson
Cube count not equal to total record count
For some reason, the record count (metadata) from OLAP is not equalling the total record count of the fact table if I count them in another application...any ideas of why?Originally posted by pdzubinski
For some reason, the record count (metadata) from OLAP is not equalling the total record count of the fact table if I count them in another application...any ideas of why?
--------------------------
If NULLS exist in your fact table and they are linked in your snoflake you will loose data b/c there is no valid link. I update all NULL values to NA and include NA in all tables to prevent this. Good Luck.
-Carla
For some reason, the record count (metadata) from OLAP is not equalling the total record count of the fact table if I count them in another application...any ideas of why?
--------------------------
If NULLS exist in your fact table and they are linked in your snoflake you will loose data b/c there is no valid link. I update all NULL values to NA and include NA in all tables to prevent this. Good Luck.
-Carla
Cube Aggregation
Simple question about OLAP Cube? Once the dimensions and cube are designed, when we process the cube, what aggregations are stored in the cube. Does the cube aggregate function defined in the measures like AggregateFunction: SUM, COUNT or is it more than that.
Another question—if the user wants to define there own aggregated function, say for example a statistical function such as standard deviation (STDEVP a MDX function) and wants to store the aggregation of that in the Cube while processing, is that possible?
Another question—if the user wants to define there own aggregated function, say for example a statistical function such as standard deviation (STDEVP a MDX function) and wants to store the aggregation of that in the Cube while processing, is that possible?
SQL Server 2005 Step-by-Step has a good walk-through on aggregation design. I believe this is in Chapter 7. Basically, you can access the aggregation wizard on the Partitions tab of your cube designer. There's quite a bit to aggregations so, again, read that chapter to get the broader picture.
Regarding STDEV or STDEVP, I believe the MDX function could be used in a calculated member to give you what you are looking for. However, it's not clear to me if it calculates the data at the level of the set you specify or if it performs the calculation across the leaf level members. Either way, you could run into an issue with calculating standard deviation across aggregated data. I recommend you assemble the components of the formula yourself, stored those as part of the cube, and perform the final calculation in a calculated member.
B.
Subscribe to:
Posts (Atom)