Showing posts with label cube. Show all posts
Showing posts with label cube. Show all posts

Wednesday, March 28, 2012

How do I access the lowest level data?

OK, this feels like a dumb question... but how do I access the

lowest-level data in the cube? I.E., before it is aggregated?

What I'm trying to do is find all of the rows where an amount is >

1000. However, when I create a calculated member or constrain in

any way, it compares to the aggregated amount. I have tried a few

ways, but they all seem to act only on the aggregated data.

Thoughts?

BTW, the SQL would be:

Select sum(measure), count(measure)

from fact_table

where measure > 1000

Thanks,

Doug

Just to understand your issue:

For example, you have sales at a daily level, some of which are <1000 some over 1000. When you roll these up to the week, month, etc , they aggregate as expected.For a given higher-level time period, do you want to sum the sales of all the days with sales<1000, and also count the number of these days? (eg in a given week where only two days are <1000 the sum might return 750 and the count 2)

It's not actually trivial:
The way I would do it would be to use DESCENDANTS to return a set of the lowest level members that are underneath your current member, then FILTER that set to pick out those where [measures].[sales] <1000 then finally SUM the values from that set.

This is a bit cumbersome to write, but will work. Check out BOL for the syntax, (or I'll post a code sample when I can check BOL to make sure I get the syntax right!)

Anyone knows a more efficient way of doing this I would appreciate it.

Richard R.

|||Thanks Richard. Your suggestion was definitely helpful, but

ultimately it ended up being way too slow because I need to span

hundreds of thousands of records, and to do that it essentially meant

creating the cartesian that described them! Anyway, what I've

done for now is to create another dimension that has ranges what the

sum of the measures are. I.E. sales range (0-49, 50-99, 100-499,

etc.). and amm using those to constrain. I guess this makes

sense, since it is a constraint, not a measure, but it is definitely

frustrating to have to do it this way. The major drawback is that

I have to now create a dimension attribute for every type of sale, and

combination of sales types, to be able to properly analyze the

data.

If anyone finds an easier way to do this, please let me know!

Thanks,

Doug|||

Hi again Doug,

I don't know if this is will be useful or not in real life, but I've found a feature in AS2005 which automatically clusters continuous variables into groups for use as dimensions - which is pretty much exactly what you want.

Create a new dimension using your fact table as the source, and your sales value as the dimension attribute. On the properties box of the attribute is a wonderfully-named property "DiscretizationBucketCount", and underneath is is "DiscretizationMethod".

The first determines the number of buckets the values will be separated into (eg 7 groups), the second the method (Automatic, Even-spaced, Clustered)

I tried it on a 14 million fact table row, asking it to cluster the value spend attribute into 7 buckets automatically, and the dimension processed in a few minutes (Fast P4 laptop 1GB RAM). The resulting clusters were not that elegant, as they had the decimal points on the range names (eg 0-15.79, 15.89 - 99.34), but they are good enough for the evaluation cubes I'm delivering to the customer tomorrow :-D

I'll dig a bit more and see if the names or clusters can be frigged manually. It's all XML behind the scenes, so I'm sure it can.

Anyone out there want to REALLY show off their expertise?

Regards,

Richard

Monday, March 12, 2012

How can we find the storage locations of partitions of a cube from its .xml file?

Hi, all experts here,

Thank you very much for your kind attention.

Could please any of you kindly advise me how can we find the storage locations of partitions of a cube from its code? (.xml file). Thanks a lot in advance and I am looking forward to hearing from you shortly for your advices and help.

With best regards,

Yours sincerely,

Hi, all experts here,

I am just a bit confused about the storage location of the objects of an analysis services database. It does not seem like it stores all the objects within the same folder the very first time when we created the analysis services database project from BI studio, since I created the project in a folder which is different from the folder where the dimensions files of a cube are stored? How it works? Please shed me some light on it and I am really looking forward to hearing from you for your kind advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||

The folder created by the BIDS project simply contains metadata files. When you deploy, process, or what not in BIDS, those metadata files are used to compile scripts that are submitted to Analysis Services. Analysis Services constructs its objects within its data folder. The root data folder can be found by openning SQL Server Management Studio, connecting to an instance of SSAS, right-clicking the SSAS instance object, and selecting Properties from the context menu. On the dialog to pops up, look for the DataDir entry.

Bryan

|||

Hi, Bryan,

Thanks for your kind advices.

With best regards,

Yours sincerely,

Friday, March 9, 2012

How can only certain objects (dimensions, cubes) be deployed?

How can only certain objects (dimensions, cubes) be deployed?

From Visual Studio I would like to deploy individual items like a dimension or cube without all of the changes in the other objects being deployed.

I don’t see any way to do that. Every time one thing gets deployed, all changes get deployed.

Visual Studio sends a Message Box Stating that the whole SSAS Database will be overwritten on the server and asks if you want to deploy or not.

We have multiple developers working and it has become a problem. We can’t deploy just those changes that are ready without overwriting the whole database.

I feel your pain...I have the same issues here (even though I'm the only developer on our BI apps). One possibility could be to have your developers work directly on the dev analysis server and add objects there. Then, you can use SSMS to individually process objects (dimensions, partitions, measure groups, etc). I'm also going to try deploying all my changes to a "staging" Analytics Database, which could be swapped out with the production DB when necessary.

It'd be great if there was a set of best-practices around the complete SSAS lifecycle.|||

While the underlying XMLA commands do support deploying single objects like a cube or dimension, it can get a bit difficult to manage due to the dependancy relationships between the various objects.

Probably a better approach with multiple developers would be to use a version control system. This way you could check-in your changes and do a "get latest" to make sure you are deploying the latest version of all the objects.

Although having said this, by remarkable coincidence I have been working on an add-in for Visual Studio that lets you deploy only the calculation script for a cube. It would probably be technically possible to extend this to deploy just a cube or dimension, but I am still concerned about the effect that this may have on dependant objects if you had multiple developers working on the same database. I still think it would be worth looking into using a version control system.

|||

We use Visual SourceSafe. The situation is as follows:

I have the Time dimension checked out and am making changes. Bob has the CashRecxeipts cube checked out and is making changes.

If I finish 1st and am ready to deploy the new Time dimension, it will invalidate all cubes that use the Time dimension. Bob will have to get the latest version of the Time dimension and fix the relatoinships for it in the CashReceipts cube before he deploys his changes.

If Bob deploys without getting the latest version of the Time dimension he will overwrite my changes.

Also, I have to modify all cubes that use the Time dimension and check their relationships to the Time dimension before I can deploy. I can't modify the CashReceipts cube because Bob has it checked out. I am at a standstill.

It is very difficult to coordinate with developers when we are in different cities.

|||

I don't see any easy answer to your question.

In your scenario, it might be possible to write something that would deploy just the altered time dimension and just the CashReceipts cube, but if you did this the CashReciepts cube would still be broken if it was not first adjusted to cater for the changes in the time dimension.

One approach that is sometimes employed (not with SSAS as far as I know, but the theory would still apply) is to have only one person (or process) that deploys to the server. They do a "get latest" from Source Safe, check that everything builds and then deploys, if anyone wants to get something new onto the server they have to check their changes in and let this person know. Ideally each developer should have their own local copy of the DB, preferrably with a small set of data so that they can process it quickly and test their changes before checking them in.

There are even tools that exist for platforms like .Net and Java, that will check the latest code out of the version control system when a new checkin is detected and build and deploy. I don't think any of these tools have hooks to SSAS, but most of them are fairly extensible. CruiseControl.Net http://ccnet.thoughtworks.com/ is one example, if you search for "Continuous Integration" you should be able to find others.

Mark Garner http://mgarner.wordpress.com/ has done some posts on his blog about applying these sorts of principles to Data Marts http://wordpress.com/tag/agile-data-warehouse/

|||

Darren,

Thanks for your suggestions and information on tools and procedures. They are good ideas and should prove helpful.