Creating an Indexed View

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

The first error message is as direct as it can be. You cannot

create a view WITH SCHEMABINDING when its definition mentions

another view that was not itself created WITH SCHEMABINDING.

In your case, you can't create 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

with schemabinding because it refers to another view,

'JJWHSE.VEW_F_INVC_SHIP_TO', which was not created with

schemabinding.

There does seem to be a bit of confusion. The code you pasted

here tries to do two things:

1. Create a view named 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'

2. Create an index on 'VEW_F_MZT_ORDER_HEADER',

which is a *different* view.

The second error you got (Cannot create index...)

has nothing at all to do with the first error or with

the CREATE VIEW code that generates the first error. It's

also as clear as it can be. The 'VEW_F_MZT_ORDER_HEADER'

exists, but it was not created with schemabinding, which

is a requirement for creating an index on a view.

If you want to index a view, that view and all the views

(and functions) on which it depends must be created with

schemabinding and meet the requirements for that option.

Steve Kass

Drew University

http://www.stevekass.com
topcoder_cc@.discussions.microsoft.com wrote:

> I am trying to create an indexed view, on a date from a date dimension

> table...I am new to SQL, and I am at a loss of ideas on this one. Any

> help would be greatly appreciated!

>

> Here is the Error I am given

>

> "Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line

> 3

>

> Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'.

> 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

>

> Msg 1939, Level 16, State 1, Line 1

>

> Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is

> not schema bound."

>

> Here is my code..

>

> CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

>

> AS

>

> SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

>

> TEW_D_LOC.LOC_DESC AS LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC AS REGION_DESC,

>

> TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

>

> ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

>

> COUNT_BIG(*) AS COUNT

>

> FROM

>

> JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

>

> ON

>

> VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

>

> AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER =

> VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

>

> AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER =

> VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

>

> INNER JOIN

>

> JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

>

> AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

>

> AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

>

> AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

>

> AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

>

> AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

>

> AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

>

> AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

>

> INNER JOIN

>

> JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

>

> ON

>

> VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

>

> AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

>

> INNER JOIN

>

> JJWHSE.TEW_D_DT TEW_D_DT

>

> ON

>

> VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

>

> INNER JOIN

>

> JJWHSE.TEW_D_LOC TEW_D_LOC

>

> ON

>

> VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

>

> WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

>

> GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY ,

> TEW_D_LOC.LOC_DESC ,

>

> TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

>

> GO

>

> CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON

> JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

>

>

creating a time dimension

this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.

i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.

i have a table with Year in column 1 and Month in column 2 mapped to the fact table.

before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.

i don't have a clue what is going on.

does anyone have a suggestion?

Just want to make sure I understand the scenario ....

You have a Time dimension table with a structure similar to this:

create table DIM.Time (

TimeID int not null identity(1,1),

Year int null,

Month int null

...

)

Your fact table has a structure similar to this:

create table FACT.MyFact (

DimID int not null,

OtherDimID int not null,

TimeID int not null,

Measure1 money null,

...

)

Is this correct?

If so, do you get the "loop" when you process the Time dimension or just when you process the cube?

Thanks,

Bryan

|||

the dim time does not have a time id just year and month.

the fact table also has year and month associated with each fact.

the fact year and month are mapped to the dim year and month.

and the dim table is used for the dimension generation.

i also tried using the fact table year and month for the dimension generation with the same result.

the loop happens when i process the cube.

|||

I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.

Not 100% certain that's the source of your exact problem, but its worth giving it a shot.

B.

|||

That worked!

thanks.

i guess this version of Analysis Services is more traditionally Relational.

sql

creating a time dimension

this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.

i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.

i have a table with Year in column 1 and Month in column 2 mapped to the fact table.

before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.

i don't have a clue what is going on.

does anyone have a suggestion?

Just want to make sure I understand the scenario ....

You have a Time dimension table with a structure similar to this:

create table DIM.Time (

TimeID int not null identity(1,1),

Year int null,

Month int null

...

)

Your fact table has a structure similar to this:

create table FACT.MyFact (

DimID int not null,

OtherDimID int not null,

TimeID int not null,

Measure1 money null,

...

)

Is this correct?

If so, do you get the "loop" when you process the Time dimension or just when you process the cube?

Thanks,

Bryan

|||

the dim time does not have a time id just year and month.

the fact table also has year and month associated with each fact.

the fact year and month are mapped to the dim year and month.

and the dim table is used for the dimension generation.

i also tried using the fact table year and month for the dimension generation with the same result.

the loop happens when i process the cube.

|||

I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.

Not 100% certain that's the source of your exact problem, but its worth giving it a shot.

B.

|||

That worked!

thanks.

i guess this version of Analysis Services is more traditionally Relational.

Creating a new measure that only sums data in specific part of cube

I have a cube with about 6 dimensions but only two are important right now. My main fact table is called MRSTATS. The dimension tables are Dim_Client#, and Dim_Test. I would like a measure called HospVol that only aggregates rows where MRSTATS.[Client#]=2. Then I need a measure called HospRVU that multiplies HospVol Measure by Dim_test.RVU. How do I got about configuring this?

Let me know if you need more information or if i'm going at this the wrong way. These values will be used in an SSRS report. Thank you very much.

I'm a little unclear on how you intend to set up your query, so my example may not be quite what you are looking for. Anyway, here is a sample query for AdventureWorks. I've broken down the calculations to make this easier to read:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[List Price] as

IIF(

[Product].[List Price].CurrentMember.MemberValue=0,

NULL,

[Product].[List Price].CurrentMember.MemberValue)

member [Measures].[Bikes Sold] as

[Measures].[Reseller Sales Amount Bikes] /

[Measures].[List Price], format="#,#"

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[List Price],

[Measures].[Bikes Sold]

} on 0,

NONEMPTY [Product].[Product].[Product].Memberson 1

from [Adventure Works]

|||

To clarify things a little bit this is for labratory testing and Dim_Section is the section of the lab where the test was performed, Dim_Test is a table of the different tests and their various billing codes. Dim_Client# is a table of different Clients who we do testing for.

Alright the rows in my table in SSRS is going to be based on members of my Dim_Section Table and then my Dim_Test table and the columns are going to be attributes of the Dim_Test table as well as a few measure groups.

The only measure I have now is Volume which is a sum of the my item_qty field. I want a measure called HospVol which is the summation of the item_qty when the client# = 2 and all so a OtherVol for rows where client# !=2

I have done no work with MDX query text just the BI SSAS interface in SQL2005.

|||

Take a look at this code. The main thing to look at are the expressions at the top. Don't get too concerned with rows and columns just yet. SSRS will "flatten" your queries so that you establish the structure in the report. Main thing is to get the formulas right for you needs.

I wrote these samples against the AdventureWorks sample SSAS OLAP database. I'd recommend working with that database a bit to get comfortable with these concepts before applying them to your cubes.

Good Luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[Reseller Sales Amount Not Bikes] as

AGGREGATE(

EXCEPT(

[Product].[Category].[Category].Members,

[Product].[Category].[Category].[Bikes]

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[Reseller Sales Amount Not Bikes]

} on 0,

NONEMPTY [Date].[Calendar].[Calendar Year].Memberson 1

from [Adventure Works]

|||

Is there a way to set up my intended measure inside Business Intelligence SSAS without using MDX?

Otherwise could you give me a little insight on what the MDX code is actually used for? Is the MDX code used in building a cube and if so where can i edit the MDX code of the cube. Or is MDX used to run queries against the cube?

This is sort of what I get out of the above query: It sets up two new measures based on one existing measure. I dont know what 'on 0' means at the end of the select statement. The query uses the three measures and aggregates each for each year in the database. (Again, i dont know what 'on 1' means) and then [Adventure Works] is probably the cube name?

Could you direct me to a webpage about MDX?

Thanks for your help and patience.

|||

So, MDX is used for two things: defining expressions and assembling cell sets. The WITH MEMBER portions at the top of the sample are the expression approach. The SELECT ... FROM portion of the sample are the cell set construction part.

The SELECT stuff is only used when building queries. In cube design, you won't take advantage of this stuff. So, if the "on 0" and "on 1" stuff don't make a lot of sense, that's OK for now.

The WITH MEMBER stuff is the foundation for the MDX you would embedded in your cube as calculated members. Take a look at Books Online for topic "CREATE MEMBER statement".

I'm not aware of any books that cover MDX really deep. You may want to browse some of the books on SSAS as they should all cover calculated members and some basic MDX queries.

The best MDX learning resource I've found is this class http://www.hitachiconsulting.com/page.cfm?ID=trainingHandsOnMDXQueries. The schedule for the class is available at http://www.hitachiconsulting.com/page.cfm?ID=trainingSchedule and you would just need to click the email link at the bottom of the page to get the details on how to register. BTW, I need to disclose I work for Hitachi Consulting, the company providing this course.

Good luck,
Bryan

|||

So I think i've found the direction I need to go with this. I think I need to be making a caluculated member to do the aggregations I need. So this is the MDX I have right now based on the example you provided and some templates I found.

Code Snippet

CREATEMEMBERCURRENTCUBE.[MEASURES].[HospVol]

ASAGGREGATE

(

EXCEPT

(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]

),

[Measures].[Item Qty]

)

FORMAT_STRING = "Standard";

The error I'm getting right now is "Error 1 Query (4, 1) Parser: The syntax for 'CREATE' is incorrect. 0 0"

How does this look to you? Thank you so much for your assistance.

|||

For anyone else looking for MDX documentation, here it is:

http://msdn2.microsoft.com/en-us/library/ms145506(SQL.90).aspx

|||

It looks like in the EXCEPT function you are trying to return all the members of your Dim Client dimension's Dim Client hierarchy except the member with a key of 2. Is that correct?

By executing the MEMBERS function against the hierarchy without specifiying the level, you will get the ALL member in your list. So, I would recommend using [Dim Client #].[Dim Client#].[Dim Client#].Members instead to return just the leaf level members and not the ALL level member.

Regarding the error, I don't know exactly what's going on with that. I would recommend creating the calculated member using the form- view of the Cube Designer's Calculations tab. This will help you get the overall syntax correct.

In the form view, you will need to give the calculated member a name, [HospVol]. (Be sure to include the brackets.)

You will need to identify it's parent hierarchy. In this case, select MEASURES.

Next, enter your expression: AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Item Qty])

Then, select your format string and visibility.

Finally, set the Non-Empty Behavior. This one is a little confusing but basically you identify a measure that when the measure is empty, the calculation is not performed. For this calculation, you can probably just ignore that.

Once you've set up the calculated member, just deploy your cube (you don't need to reprocess if the cube is already processed).

Good luck,
Bryan

|||

Hmm, I was going for nothing but Client# 2 so i'll have to look at that. It turns out that i'm going to need the except client# 2 so this one is still useful. I actually am using the form in the cube designer so thats probably why it was giving me the error with the create member.

I put in your expression and it evaluated but the aggregations are wrong because the values are higher than that of the total volume. I have confirmed that [Volume] has correct values so it must be a problem with the calculated member.

Here is my exact expression

Code Snippet

AGGREGATE(

EXCEPT(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

Could it be a problem with how is aggregating? That should be based on [Volume] though correct?

If it clears up anything with the Client# dimension, there are to attributes, [Dim_Client#] and [Org Client]. There's not exactly much of a heirarchy going on.

Would the aggregation of just Client# 2 be:

Code Snippet

AGGREGATE(

FILTER(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

This calculation takes a long time and returns blank cells. Maybe FILTER is the wrong keyword.

Am I going to run into any problems creating calculated members inside a non-measure dimension that multiply an measure by an attribute of the dimension?

Aside from this stuff, I think i'm pretty much ready to take this on by myself. Thanks for all your help

|||

Your expression needs to build a set of members and then cross join it to the measure of interest. This will generate a set of measure values associated with each member. That set can then be aggregated into a single value.

So, in the case where you want to get the set of all members that are NOT client key 2, the EXCEPT expression will give you that. If you want the set of just client 2, then just ask for that one member. Here is the set definition for each of these:

Code Snippet

EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2])

Code Snippet

[Dim Client#].[Dim Client#].&[2]

So, we have a set of members. Now we need to cross join this to the measure of interest, [Measures].[Volumes]. This will give us one measure value for each member in the set. If we have just one member in the set, SSAS can return just that one value. If we have multiple members, we need to aggregate those values to get a single, returnable value.

Code Snippet

AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Volumes])

Code Snippet

([Dim Client#].[Dim Client#].&[2],[Measures].[Volumes])

As for the syntax problems, I'd kinda need to play with the cube to figure that one out. That's why we often use the WITH MEMBER syntax on a SELECT statement to get these things defined before moving them into the cube.

Anyway, hope that helps. Good luck.

Bryan

Creating a new measure that only sums data in specific part of cube

I have a cube with about 6 dimensions but only two are important right now. My main fact table is called MRSTATS. The dimension tables are Dim_Client#, and Dim_Test. I would like a measure called HospVol that only aggregates rows where MRSTATS.[Client#]=2. Then I need a measure called HospRVU that multiplies HospVol Measure by Dim_test.RVU. How do I got about configuring this?

Let me know if you need more information or if i'm going at this the wrong way. These values will be used in an SSRS report. Thank you very much.

I'm a little unclear on how you intend to set up your query, so my example may not be quite what you are looking for. Anyway, here is a sample query for AdventureWorks. I've broken down the calculations to make this easier to read:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[List Price] as

IIF(

[Product].[List Price].CurrentMember.MemberValue=0,

NULL,

[Product].[List Price].CurrentMember.MemberValue)

member [Measures].[Bikes Sold] as

[Measures].[Reseller Sales Amount Bikes] /

[Measures].[List Price], format="#,#"

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[List Price],

[Measures].[Bikes Sold]

} on 0,

NONEMPTY [Product].[Product].[Product].Memberson 1

from [Adventure Works]

|||

To clarify things a little bit this is for labratory testing and Dim_Section is the section of the lab where the test was performed, Dim_Test is a table of the different tests and their various billing codes. Dim_Client# is a table of different Clients who we do testing for.

Alright the rows in my table in SSRS is going to be based on members of my Dim_Section Table and then my Dim_Test table and the columns are going to be attributes of the Dim_Test table as well as a few measure groups.

The only measure I have now is Volume which is a sum of the my item_qty field. I want a measure called HospVol which is the summation of the item_qty when the client# = 2 and all so a OtherVol for rows where client# !=2

I have done no work with MDX query text just the BI SSAS interface in SQL2005.

|||

Take a look at this code. The main thing to look at are the expressions at the top. Don't get too concerned with rows and columns just yet. SSRS will "flatten" your queries so that you establish the structure in the report. Main thing is to get the formulas right for you needs.

I wrote these samples against the AdventureWorks sample SSAS OLAP database. I'd recommend working with that database a bit to get comfortable with these concepts before applying them to your cubes.

Good Luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Category].[Bikes])

member [Measures].[Reseller Sales Amount Not Bikes] as

AGGREGATE(

EXCEPT(

[Product].[Category].[Category].Members,

[Product].[Category].[Category].[Bikes]

),

[Measures].[Reseller Sales Amount]

)

select

{

[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes],

[Measures].[Reseller Sales Amount Not Bikes]

} on 0,

NONEMPTY [Date].[Calendar].[Calendar Year].Memberson 1

from [Adventure Works]

|||

Is there a way to set up my intended measure inside Business Intelligence SSAS without using MDX?

Otherwise could you give me a little insight on what the MDX code is actually used for? Is the MDX code used in building a cube and if so where can i edit the MDX code of the cube. Or is MDX used to run queries against the cube?

This is sort of what I get out of the above query: It sets up two new measures based on one existing measure. I dont know what 'on 0' means at the end of the select statement. The query uses the three measures and aggregates each for each year in the database. (Again, i dont know what 'on 1' means) and then [Adventure Works] is probably the cube name?

Could you direct me to a webpage about MDX?

Thanks for your help and patience.

|||

So, MDX is used for two things: defining expressions and assembling cell sets. The WITH MEMBER portions at the top of the sample are the expression approach. The SELECT ... FROM portion of the sample are the cell set construction part.

The SELECT stuff is only used when building queries. In cube design, you won't take advantage of this stuff. So, if the "on 0" and "on 1" stuff don't make a lot of sense, that's OK for now.

The WITH MEMBER stuff is the foundation for the MDX you would embedded in your cube as calculated members. Take a look at Books Online for topic "CREATE MEMBER statement".

I'm not aware of any books that cover MDX really deep. You may want to browse some of the books on SSAS as they should all cover calculated members and some basic MDX queries.

The best MDX learning resource I've found is this class http://www.hitachiconsulting.com/page.cfm?ID=trainingHandsOnMDXQueries. The schedule for the class is available at http://www.hitachiconsulting.com/page.cfm?ID=trainingSchedule and you would just need to click the email link at the bottom of the page to get the details on how to register. BTW, I need to disclose I work for Hitachi Consulting, the company providing this course.

Good luck,
Bryan

|||

So I think i've found the direction I need to go with this. I think I need to be making a caluculated member to do the aggregations I need. So this is the MDX I have right now based on the example you provided and some templates I found.

Code Snippet

CREATEMEMBERCURRENTCUBE.[MEASURES].[HospVol]

ASAGGREGATE

(

EXCEPT

(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]

),

[Measures].[Item Qty]

)

FORMAT_STRING = "Standard";

The error I'm getting right now is "Error 1 Query (4, 1) Parser: The syntax for 'CREATE' is incorrect. 0 0"

How does this look to you? Thank you so much for your assistance.

|||

For anyone else looking for MDX documentation, here it is:

http://msdn2.microsoft.com/en-us/library/ms145506(SQL.90).aspx

|||

It looks like in the EXCEPT function you are trying to return all the members of your Dim Client dimension's Dim Client hierarchy except the member with a key of 2. Is that correct?

By executing the MEMBERS function against the hierarchy without specifiying the level, you will get the ALL member in your list. So, I would recommend using [Dim Client #].[Dim Client#].[Dim Client#].Members instead to return just the leaf level members and not the ALL level member.

Regarding the error, I don't know exactly what's going on with that. I would recommend creating the calculated member using the form- view of the Cube Designer's Calculations tab. This will help you get the overall syntax correct.

In the form view, you will need to give the calculated member a name, [HospVol]. (Be sure to include the brackets.)

You will need to identify it's parent hierarchy. In this case, select MEASURES.

Next, enter your expression: AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Item Qty])

Then, select your format string and visibility.

Finally, set the Non-Empty Behavior. This one is a little confusing but basically you identify a measure that when the measure is empty, the calculation is not performed. For this calculation, you can probably just ignore that.

Once you've set up the calculated member, just deploy your cube (you don't need to reprocess if the cube is already processed).

Good luck,
Bryan

|||

Hmm, I was going for nothing but Client# 2 so i'll have to look at that. It turns out that i'm going to need the except client# 2 so this one is still useful. I actually am using the form in the cube designer so thats probably why it was giving me the error with the create member.

I put in your expression and it evaluated but the aggregations are wrong because the values are higher than that of the total volume. I have confirmed that [Volume] has correct values so it must be a problem with the calculated member.

Here is my exact expression

Code Snippet

AGGREGATE(

EXCEPT(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

Could it be a problem with how is aggregating? That should be based on [Volume] though correct?

If it clears up anything with the Client# dimension, there are to attributes, [Dim_Client#] and [Org Client]. There's not exactly much of a heirarchy going on.

Would the aggregation of just Client# 2 be:

Code Snippet

AGGREGATE(

FILTER(

[Dim Client#].[Dim Client#].Members,

[Dim Client#].[Dim Client#].&[2]),

[Measures].[Volume])

This calculation takes a long time and returns blank cells. Maybe FILTER is the wrong keyword.

Am I going to run into any problems creating calculated members inside a non-measure dimension that multiply an measure by an attribute of the dimension?

Aside from this stuff, I think i'm pretty much ready to take this on by myself. Thanks for all your help

|||

Your expression needs to build a set of members and then cross join it to the measure of interest. This will generate a set of measure values associated with each member. That set can then be aggregated into a single value.

So, in the case where you want to get the set of all members that are NOT client key 2, the EXCEPT expression will give you that. If you want the set of just client 2, then just ask for that one member. Here is the set definition for each of these:

Code Snippet

EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2])

Code Snippet

[Dim Client#].[Dim Client#].&[2]

So, we have a set of members. Now we need to cross join this to the measure of interest, [Measures].[Volumes]. This will give us one measure value for each member in the set. If we have just one member in the set, SSAS can return just that one value. If we have multiple members, we need to aggregate those values to get a single, returnable value.

Code Snippet

AGGREGATE(EXCEPT([Dim Client#].[Dim Client#].Members,[Dim Client#].[Dim Client#].&[2]),[Measures].[Volumes])

Code Snippet

([Dim Client#].[Dim Client#].&[2],[Measures].[Volumes])

As for the syntax problems, I'd kinda need to play with the cube to figure that one out. That's why we often use the WITH MEMBER syntax on a SELECT statement to get these things defined before moving them into the cube.

Anyway, hope that helps. Good luck.

Bryan

Creating a dimension tablr

Hi Guys
Fairly new to this, I'm having a problem creating a hierarchial dimensionn
from a table with the following structure:
STATE | ZONE | BRANCH | CONTACT TYPE | CONTACT
--|--|--|--|--
9 records|7 records | 25 records | 6 records |3984 records
I need the structure to be as follows:
STATE
--ZONE
--BRANCH
--CONTACT TYPE
--CONTACT
I can't seem to get this right, I've created the dimension and added the
level above as an attribute to the current level. e.g.:
ZONE
--Attribute STATE
BRANCH
--Attribute ZONE
etc.
Then created the hierarchy as specified above, the dimension processes, but
it doesnt display correclty, all the properties on the dimension are set to
default, anyone care to help me?
I hope the above makes sense.
I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"
|||Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Creating a Dimension Table from a 3-key table

Hi All,

I have a situation with a table that was created for a transactional

system with a 3 columns key. The table is similar to the following:

country state city description
1 12 21 City A from country 1 and state 12
1 13 21 City A from country 1 and state 13
2 14 22 City B from country 2 and state 14
2 15 22 City B from country 2 and state 15

Now I'm trying to create a dts package that would allow me to build a

city dimension table with unique codes (keys) for each city. What kind of

transformation should I use to translate the old codes (based on the

country-state-city key) into the new ones and preserving the data

integrity?

Thanks,

Ignaciodoesn't that defeat the purpose of building a cube?

What's it going to be for?

How are you going to go after the data?|||This cube is going to show sales history since 2002. The table where data is being pulled could contain data as the following:

cust_id year country state city amount_cash amount_credit
525 2002 1 12 21 8500 3200
714 2002 1 13 21 3250 775

Let's say I create a fact table with fk and measures only. If I would like to know city totals, it looks like cities sharing the same id will be aggregated, when in fact they shouldn't. And in the other hand, cities that are geographically shared by different states will add up correctly. Maybe I need to further analyze this, but what first puzzled me was the city table with no unique id.

Thanks for your thoughts!

Ignacio

Creating a dimension based on uniqueidentifier

Is it possible to base a dimension on a uniqueidentifier? My company uses uniqueidentifiers for all primary keys in our tables. However, I am now trying to create a parent-child dimension in Analysis Manager using one of our tables but I always get the error

Unable to count the members of the 'PK' level.

Unable to open the record set.

Error: The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument.

Any suggestions?Note: this is in AS 2000.

Anyone know if this is possible in 2005?

Creating a calculated measure that filters

Hi,

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'? That way, I have a total count and a New customer count.

Thanks,

Brian

This should be very easy to do in MDX if you have attribute NewCustomer in the Customer dimension. The expression then would be something like

(Measures.[Sales Count], Customer.NewCustomer.[true])

|||

Thanks Mosha, indeed it is easy! I'd like to expand on this.

So, by adding a new calculated member, (Measures.[Sales Count], Customer.NewCustomer.[true]), it will give me the counts of new customers. Is is possible to now also filter on, say, customerStatus as well? eg.

My Sales cube also has a CustomerType dimension. How can I expand the filter above to also filter on the CustomerType dimension (say, customertype = E).

I'm still trying to get my head around MDX :)

Thanks!

Brian

|||I wouldn't call this "expansion" of the filter, it is more of "restricting" the filter further. Yes, you can add as many hierarchy members to the filter as you want. I recommend going over first few chapters of my "Fast Track to MDX" book to get yourself on the MDX track fast...

Creating a 4-4-5 Time Dimension

Hello:

Very soon my company will be moving to a 4-4-5 reporting schedule. Basically, what this means is that the first month of the quarter will have 4 weeks, the second will have 4 weeks, and the third will have 5 weeks. Therefore, for the 2007 the dates for Jan, Feb and Mar will be as follows:

Jan - 1 - 27

Feb - 28 - 24

Mar - 25 - 31

Currently, I have an SSIS package creating a record for each day in the Time Dimension. Is there any script out there that will help me build a Fiscal calendar such as the one described above?

I realize that this is not a direct SSIS question but I figured that some of you might have encountered this situation and hence my post.

Thank you!

Phew. That doesn't sound easy - other than just looping over the weeks I don't see how it could be done. I suggest you post this on the T-SQL forum. I bet there's some people on there that would love to have a go at this.

-Jamie

|||I agree wit Jamie, the T-SQL forum is a better place for that. We have handle those cases in the past via stored procedure, but sorry I don't have a sample now. This a fairly common scenario and I am pretty sure there have to be some sample code out there.|||

Thank you for your response! I will close this one out.