Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
GuangmingI believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
..
<column list>
..
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] a
re on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I do this by MDX?

My fact table has a field QtyReleased. I would like to know the number of
records with QtyReleased > 1000.
Can we do this in SQL 2005 by MDX?
Guangming
I believe you can, but do you really need to do this with MDX? Why cant you
handle this on transactional side? Just make a field that contains 1 if
QtyReleased > 1000 and 0 if not. Then all you have to do is make a measure
from that field with a simple sum aggregate.
If you really need to do this with MDX look at the count functions..
MC
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:6AAD5BCE-C77C-4D7E-BC0A-328628576F49@.microsoft.com...
> My fact table has a field QtyReleased. I would like to know the number of
> records with QtyReleased > 1000.
> Can we do this in SQL 2005 by MDX?
> Guangming
>
|||MC is right, if you want a "record" count, add a calculated field on the
relational side and create a simple measure in the cube.
If however you want to calculate which hierarchy members have
QtyReleased > 1000, you would do this via MDX.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||I think the problem is coming from QtyReleased and Measure Count in same
measure dimention (fact table).
The rule is changing (QtyReleased > 1000 - > QtyReleased > 1500). I would
like to do it by MDX.
Count() does not take any condition like IIF. I tried both and not working.
Do you have any more thoughts?
Thanks,
Guangming
"Darren Gosbell" wrote:

> MC is right, if you want a "record" count, add a calculated field on the
> relational side and create a simple measure in the cube.
> If however you want to calculate which hierarchy members have
> QtyReleased > 1000, you would do this via MDX.
> --
> Regards
> Darren Gosbell [MCSD]
> <dgosbell_at_yahoo_dot_com>
> Blog: http://www.geekswithblogs.net/darrengosbell
>
|||I still think MC's original suggestion is probably what you are after.
What you do is to create a view over your fact table and use the view in
your cube instead of using the fact table directly.
The view would look something like the following:
SELECT
...
<column list>
...
, CASE WHEN QtyReleased > 1000 THEN 1 ELSE 0 END AS QtyOverThreashold
FROM FactTable
Then you create a simple sum based measure over the QtyOverThreashold
column from the view.
If still think you need to do this in MDX (and you may - it's hard to
fully understand your situation over a newsgroup ). Then you need to
use the FILTER() function to give you the IIF type logic.
Assuming that you want to know the number of products with QtyReleased >
1000 the MDX would look something like this...
COUNT(
FILTER(
DESCENDANTS([Products].CurrentMember,,LEAVES)
,[Measures].[QtyReleased] > 1000
)
)
This gets the leaf level descendants of the currently selected member of
the product dimension and counts the number of members with QtyReleased
> 1000
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||> I tried the Count() one, which did not work. In my case
> [Measures].[QtyReleased] , and [Measures].[MeasureCount] are on Fact table
> as Measures, not as Dimensions.
> So I guess it does not work.
>
In that case just go with my first suggestion of creating a view with a
calculated column and create a measure in the cube off that.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
GuangmingIf you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com>
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx[/ur
l]
> when it discusses partitioning. It is also discussed extensively in the SQ
L
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.co
m>
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com..
.
>
>|||
> In management studio, go to cube - partition, right click you'll get 'merg
e
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you c
an
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbellsql

can I combine several partitions into one in a cube?

Hi,
I got 4 partitions corresponding to four fact tables.
Does it affect cube query performance? most of time, i need to access all
partitions.
If so, how can I combine them into one partition?
Thanks,
Guangming
If you are using MOLAP storage (which should be mostly the case) then the
underlying relational fact tables are never touched during a query.
Partitioning is very important for smooth even response times. See the AS
Performance Guide here:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
when it discusses partitioning. It is also discussed extensively in the SQL
Server 2000 Resource Kit which has an entire chapter on partitioning.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
> Hi,
> I got 4 partitions corresponding to four fact tables.
> Does it affect cube query performance? most of time, i need to access all
> partitions.
> If so, how can I combine them into one partition?
> Thanks,
>
> Guangming
|||In management studio, go to cube - partition, right click you'll get 'merge
partition ...'.
it seems that if there are multiple partitions in one measure group, you can
merge them. If partitions are for different measure groups, you can not.
Most time, merging is not necessary, I guess.
Guangming
"Dave Wickert [MSFT]" wrote:

> If you are using MOLAP storage (which should be mostly the case) then the
> underlying relational fact tables are never touched during a query.
> Partitioning is very important for smooth even response times. See the AS
> Performance Guide here:
> http://www.microsoft.com/technet/pro.../ansvcspg.mspx
> when it discusses partitioning. It is also discussed extensively in the SQL
> Server 2000 Resource Kit which has an entire chapter on partitioning.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI Systems Team
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Word 2003 memory Leakage" <Word2003memoryLeakage@.discussions.microsoft.com >
> wrote in message news:F71962A3-6EE7-45E8-A80B-CCB22DBA8F51@.microsoft.com...
>
>
|||
> In management studio, go to cube - partition, right click you'll get 'merge
> partition ...'.
> it seems that if there are multiple partitions in one measure group, you can
> merge them. If partitions are for different measure groups, you can not.
Usually you have different measure groups, because the tables have
different columns. So by definition you have a different structure in
the partition therefore it does not make sense to merge them
If you do have multiple measure groups of identically structured fact
tables then they probably should be setup as multiple partitions under
the on measure group, rather then multiple measure groups each with a
single partition.

> Most time, merging is not necessary, I guess.
In AS2k, the server would read and resolve the data from each partition
using a separate thread so having multiple partitions can give you a
performance boost, especially on a multi processor machine with a fast
disk sub system.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Can I add a named calculation to a measure group

Hi everyone,

Here's my question:

I add a named calculation to one fact table, let's say total cost = units * cost

After doing so, I add this named calculation to a measure group with a sum aggregation method. But while I was processing the cube, an error occurred, which said "OLAP storage engine error..."

Is there anything I had done wrong? or there's better way to achieve the require.

Thanks,

It's possibble to add a named calculation to a measure group...

But If you want to aggregate as sum and generates an error, check the datatypes of the fields Units and Cost... maybe be you need to do a CONVERT... try to explore your facttable and see if there is any error...

Give more details...

regards!!

|||Hello Pedro, thanks for the reply.

I tried it again, and it works fine without an error this time.
|||

I'm happy you get it!

Mark your answer as resolved!

regards!!

Can Fact table link to more than 16 dimensions?

Hi,

I am new to building cubes. I am trying to build a cube which includes:

7 database dimensions

22 cube dimensions (19 cube dimensions are liked to 4 table dimensions)

The problem is when I try to make the PK of the fact table to include the 22 fields I receive an error that the PK cannot be more than 16 fields. What should I do?

Thanks in advance,

Aref

It is a bit strange design. You should not have that many cube dimensions in you only have 7 database dimensions.

Make sure you take a look at the AdventureWorks sample database for example of how to build your cube.

Going through tutorial is also a good idea.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I might be wrong. But I have 6 date cube dimensions which I relate to one date database dimension.

Aref

|||

Do you have all 6 columns in your fact table so you can relate Date dimension to your Measure Group?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes I have the 6 cube dimension fields in my fact table. But what I know about star schema fact tables is that all dimension fields should be PK. Am I wrong?

|||

You are correct. That is the recommendation in most cases.

I dont think you should try and create a single PK including 6 columns. You can create 6 separate PK's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

But these 6 columns are only part of the 22 dimension fields that I have.

I am thinking of creating another table that holds all the possible combination of these multiple dimensions related to one database dimensions the use then use the PK of that combination as a single dimension field in my fact table then use a view to represent my fact table and the dates combinations table in the cube.

Do you think that's a good way to solve the issue or you have better suggestions?

Thanks,

Aref

|||

Take a look at the AdventureWorks sample database and see how it implements Date dimension.

It is single dimension in the database but included 3 times in the cube. Every time it is playing different role Date, ShipDate, DeliveryDate.

If you look at the way relationship between FactSalesSummary table and DimTime table are defined you would see 3 FK- PK relationships.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

That's what I have so far, but the problem if I want to include every cube dimension in a PK FK relation I need to have a number of PK fields equal to my cube dimensions = 22 fields which violate the SQL Server 2005 limitation of number of fields in a PK.

Please Help!

Aref

|||

Are you trying to create a single PK with 22 fields or 22 distinct PK's?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I don't know what is the difference. I want to define 22 fields in my fact table to be the PK for this table. In Star schema I gather all the PKs of the cube dimensions to be the PK of my fact table. Right?

Can fact table include fields that are neither dimension nor measurements?

I am new to Dimensional Modeling in SSAS!

Can fact table include fields that are neither dimension nor measurements?

I need this for a drillthrough Action that shows data other than the dimensions or the measurements!

Please help!

Aref

The answer is NO.

You cannot drill through into something that is not part of your model ( ether dimension or measure group measure)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

So, there are something columns (like some detail information or description) in the fact table. And these columns must show in drill through action.

The fact table must become as a dimension?

|||

Take a look at the dimension with type Fact.

Looks like in your case you should be looking at creating new dimension with type Fact and adding the columns you would like to see in the drillthrough to this dimension as attributes.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Can fact table include fields that are neither dimension nor measurements?

I am new to Dimensional Modeling in SSAS!

Can fact table include fields that are neither dimension nor measurements?

I need this for a drillthrough Action that shows data other than the dimensions or the measurements!

Please help!

Aref

The answer is NO.

You cannot drill through into something that is not part of your model ( ether dimension or measure group measure)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

So, there are something columns (like some detail information or description) in the fact table. And these columns must show in drill through action.

The fact table must become as a dimension?

|||

Take a look at the dimension with type Fact.

Looks like in your case you should be looking at creating new dimension with type Fact and adding the columns you would like to see in the drillthrough to this dimension as attributes.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.