calculated members, calculation process

[dky1e]

Hi,

From what I've read (and understand) Calculated Members on a cube are determined on runtime.

Performing these calculations might cause performance problems with my implementatin. Is it possible to save these values the same way aggregates are saved?

Thanks,

Kyle.

 

[MDXer]

The short answer is No. Calculated members can't be calculated prior to run time. I don't see how calcs could cause a performance problems on cubes that are optimized, and designed for performance. I personally have worked cubes in the +100GB range and had clacs that would return faster than the MDX sample app could render it results.

What are you performance limitations?
Are your cube designs targeted to your reporting needs and performance needs?
What measures types are contained within your cubes?
What measure types are your Calcs based upon?
Have you targeted your aggregations to reporting needs?

Many things going into making a cube preform to expectation and it can take a long time to tune the server and cube to meet preformance requirements and expectations.

Unfortunately their are very few resources detailing techniques for building high preforming OLAP cubes. OLAP especially MS analysis services usually gets a couple chapters at the end of a SQL book. Which means a lot of trial and error goes into building cubes to meet a users needs. Which makes forums like this all the more valuable.




"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dky1e]

Thanks for your response MDXer, you seem to be the guru of this forum and I value your efforts to help. And you're right about the trial and error stuff, a week there of

Right now the calculated member is an activex component, that takes in the current member selected and returns a value.

The mebmer's value is based on the current members and is retrieved from database, so for example a user can be looking at 'New York', 'All Stores', 'Sony', 'All Colors' and the member will return a unique number of customers that own this option, then another member returns the Mode Price they paid for the options selected and then another one returns the number of unique customers, and then number of unique customers that paid under the price, etc....

From what I undestand unique counts are not available from calculated members, that is why I have to do this circle of calling ax component which in turns goes back to the data.

If you can think of better implementation ideas let me know. I wish there could be more resources/documentation on OLAP analysis.

Thanks,

Kyle.

 

[MDXer]

Lets work this issue one step at a time. starting with

The mebmer's value is based on the current members and is retrieved from database, so for example a user can be looking at 'New York', 'All Stores', 'Sony', 'All Colors' and the member will return a unique number of customers that own this option

For distinct count values try using the distinct count aggregation type in the cube editor. BUT BEFORE YOU DO THAT! please read on.

A cube can have only 1 distinct count measure, in reality it should be the only measure.

In your scenario you are first looking to get the number of Unique (Distinct) Customers. I assume (I hate that word) that you have a CustomerID or some such column that you are keying your dimesnion to, and hopefully your using surogate keys). To get the Count of unique customers you would build a cube that dimensionaly mirrors you base cube but contains a measure that is keyed to you CustomerID column and has an aggregation type of Distinct Count. You can then join this cube with your base cube in a virtual cube. Probably the fastest way to do this is copy your base cube and paste into your database changing the name edit this new cube deleting all measures and then add the distinct count measure.


now that you have a measure that returns your distinct customers built into the cube you can cross join it with other dimensions allowing you to get a distinct number of customers for your options (if an option dimension exists).

One thing to remember is that you now have 2 cubes to process when you load data, but the over all build time should be pretty low for the preformance you should gain.

One of these days I will write a FAQ on distinct counts

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dky1e]

Thanks. I'll give it a try.

Let's say I have a measure MinPrice which is the min aggregate and MaxPrice which is the max aggregate. Now I want to do distinct count of customers that paid minprice and another member with number of unique customers that paid the max price. So... I'll need two additional cubes and somehow I need to pass in the [min or max] price so that that it'll know what to count. I don't think it's possible.

Egh... Mess.

 

[MDXer]

You won't need another cube. Your MIN and MAX aggregate types do not work the same way that the distinct count does. The reason for making distinct count cubes seperate is more of making it easier for the engine to deal with them.

to deal with the MinPrice and MaxPrice issue is that you could have a Min MaxDimension. that you would key to a column in your Fact table.

If you distinct your min max columns you won't get the customers count you will get the count of distinct prices.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dky1e]

Let me make it a little clearer. Calculating Min/Max is not the my issue here, I am having problems with the distict counts, as you can see below
(I hope I'm a little less ambiguous this time)

In the fact table I have two fields, one for the price called [Price] and one for the CustomerID [CustomerID].

The user wants to see:
1.total #of purchases, just a count.
2.min price, easy
3.max price, easy
4.avg price, easy
5.mode price (pain in the butt, that is why i created the ax control in first place)
6.number of customers, the dist. count on customerid, still easy...
7.number of customers that paid the min price
8.number of customers that paid above the min price
8.number of customers that paid the max price
and so on for each price aggregate

Now as you see I need to calculate (somehow) the number of customers (distinct count) multiple times in the cube and for each of the price measures.

 

[MDXer]

while I think about items 7,8,9 can you post the definition of mode price and the formula you get the result from?

Also you in your fact table I am sure you carry your product as well.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dky1e]

Mode price is the price that occurs most frequently, if more than one price appears with same frequency then lowest is taken, if each price appears only once, then avg of all is used. I implemented it with the ax control and a sql stored procedure. And to make it more fun business rules require me to count orders, so if someone purchases 10 items for the given price it is counted only once, since it's only one order.

And yes, i have a "productid" type field in the fact table.

As you can see now, the whole process of distinct counts is quite time consuming and it's driving me crazy now I've created a table that caches most of the possible results and adds to it as users drill though the cube, but it's still too slow.

Thanks.

 

[MDXer]

Didn't forget about this problem I was away for the weekend. I'll look at the above info and reply a little later.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MDXer]

I just want to Clarify here (trying to get back in the groove) You do or do not need the Number of orders at each price between min and max?

Min - $5.00
Max - $10.00

2 orders - $5.00
3 orders - $7.00
2 orders - $8.50
6 orders - $10.00

Min Max Above Min
--- --- ---------
2 6 5

or do you need it broken out at each distinct Dollar amount?

If it is the first scenario then one way to achieve this would be to add member properties to you products for Min and Max Price. You could then do a calculated measures that would count everything equal to min price everything equal to max price and everything between the do. If the second situation is what your after you may be better served building a dimension with the distinct values of price and then use the dimension in the report qualified using NON Empty so it will only disply prices members that contain data. Mode Price is tricky.

Unfortuantely reporting requirements can sometimes make designs challenging and this is doubly so when performance is an issue. I think you have a great start at meeting your users needs and with some tweaking major or minor you can deliver the needed information.

I think a key point to remember is that a cude design or BI implementation does not happen over night. They often go through many iterations before the design is finalized. If you have a working design that people can use and is acceptable then allow them to use that one while you make changes, if the result is better performance in a newer design I don't think anyone would complain.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dky1e]

Thanks for your very optimistic response. Let's Rock and Roll!

I have been trying to get it up and running but it seems like I keep on running into walls... it's hard to explain to management that it is simply just the way it has to be and more time needs to be invested into design/implementation. The best is the time i spent is great experience for me as it is a fairly difficult especially that I am a complete novice to MDX and cubes in general.

Regarding the above. The results of Min=5, Max=10, above Min = 5 is what is expected (to change with out notice, as the management tends to "improve" the requirements).

For example product "A" could be sold anywhere from $5 to 10 and "blue" products might range in $2 to $100. Can you give me more detail on adding properties to members, as I don't understand how it can help me?

In the mean time I've created a flat table which stores most of the possible combinations and results for the min/max/mode prices and counts. I've noticed that the activeX object does not get destroyed on runtime and I can access the same object and it's data from multiple members. This gives me much performance improvement as the activeX component goes to the table and picks up all the data that will be displayed and puts it into temp vars. The function is called with the first calculated member, then the other members just pick up the data stored in the temp variables. This allows for only one trip back to the db rather than a trip for every calculation.
This is just a temporary solution, as it defies the idea of a data cube and I really want to implement it using only cube structures to have less maintenance issues in the future. It allows the management to get the feeling of what cubes are about, being able to drill through data in real-time with out bothering me all the time!

Once again, I want to sincerely thank you for your interest and uncomparable enthusiasm in this forum.

Kyle.

 

[MDXer]

Kyle,

Again sorry for the delay in a reply.

In Regards to Member porperties.

Member Properties are added to dimension levels using the Dimension editor. Member properties can represent pretty much anything you want or need them to, but it is advised that you use member properties only when neccessary as they live in memory while analysis services is running. Member properties are most commonly used in the building of Virtual Dimensions and in Calculated Members (Measures).

Creating member properties:

1) Open the appropriate Dimension in the Dimension editor.
2) In the Tree View Expand the level at which you want to
add the Members. You should see a folder Titled "Member
Properties"
3) Drag the Column that serves as the property onto the
Member Properties folder.
4) Give them a meaningful name.
5) Process the dimension.

If the dimension is already processed you can do an Incremenatal process and it will not affect any cubes.

Using member Properties is as complex as the calculation requires.

For example if you have a Product Dimension and you assign a member property for the Manufacturer at the Product level you can display it as a calculated member by simply using [Products].[Product Group].[Product].CurrentMember.Properties("Manufacturer&quot.

SO in your case if we add 2 member properties "Max Price" and "Min Price" you could create you measures Max Price Count, Min Price Count and Avg. Price count. by companrin the cell value with the member property.

The exact manner I haven't played with yet but would probably be similiar to

IIF([Measures].[Sale Price] = [Product].CurrentMember.Properties("Min Price&quot, Measures.[Cust Count], Null)

Like I said the actual calculation would need some work but this kind of gives the gist of what would be done.

In regards to mode price I am not 100% but my feeling is this too can be done within the cube with a little work.

Do you have George Spoffords MDX Book? If not I highly recommend it.


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck