How concatenate members from 3 virtual dimensions using MDX

Hello,
There is a big problem - my client made 3 virtual dimensions (Year, Quarter,
Month) from single time dimension and wants to use them, because the
graphical interface in Excel is more friendly for end-users to have 3
dimensions (i.e. 3 separate tabs).
But in the same time we need to make MDX queries with time series with MDX
formulas like YTD, LastPeriod and so on. These formulas need full time
dimension.
I wonder there is possibility to concatenate all 3 members from virtual
dimension and use that aggregate as member for real Time dimension. For
example if user selects 2005 year, I Quarter and February from virtual
dimensions, we can assume, that member from real dimension could be
[2005].[I].[February], but how make that member and submit it to
formula for
later use.
In SQL we have possibility to construct SQL queries dynamically, and what
about MDX.
ErnestasJust an idea -
StrToTuple(
IIF( < original tuple >.Item(0).Dimension IS [VYear] AND < tuple
>.Item(0).Dimension IS [VQuarter] AND < tuple >.Item(0).Dimension IS
[VMonth], < Parse the unique names to create your real unique name using
VB
functions>, TupleToStr(<original tuple> ) )
)
"Ernestas" <sysojevas@.delfi.lt> wrote in message
news:efUjpNHNFHA.244@.tk2msftngp13.phx.gbl...
> Hello,
>
> There is a big problem - my client made 3 virtual dimensions (Year,
> Quarter, Month) from single time dimension and wants to use them, because
> the graphical interface in Excel is more friendly for end-users to have 3
> dimensions (i.e. 3 separate tabs).
>
> But in the same time we need to make MDX queries with time series with MDX
> formulas like YTD, LastPeriod and so on. These formulas need full time
> dimension.
>
> I wonder there is possibility to concatenate all 3 members from virtual
> dimension and use that aggregate as member for real Time dimension. For
> example if user selects 2005 year, I Quarter and February from virtual
> dimensions, we can assume, that member from real dimension could be
> [2005].[I].[February], but how make that member and submit it
to formula
> for later use.
>
> In SQL we have possibility to construct SQL queries dynamically, and what
> about MDX.
>
> Ernestas
>|||Correction:
StrToTuple(
IIF( < original tuple >.Item(0).Dimension IS [VYear] AND <
tuple.Item(1).Dimension IS [VQuarter] AND < tuple >.Item(2).Dimension IS
[VMonth], Parse the unique names to create your real unique name using
VB
functions>, TupleToStr(<original tuple> ) )
)
"Elad" <elad> wrote in message news:OZh2t9HNFHA.2372@.TK2MSFTNGP10.phx.gbl...
> Just an idea -
> StrToTuple(
> IIF( < original tuple >.Item(0).Dimension IS [VYear] AND < tuple
> [VMonth], < Parse the unique names to create your real unique name usi
ng
> VB functions>, TupleToStr(<original tuple> ) )
> )
>
> "Ernestas" <sysojevas@.delfi.lt> wrote in message
> news:efUjpNHNFHA.244@.tk2msftngp13.phx.gbl...
>

How change name of level (All)

Hi,
I’m creating dimension with SSAS 2005 – Business Intelligence Development
Studio. Dimension has attribute hierarchies and user hierarchies.
For attribute hierarchies and user hierarchies I can define my own all
member name ( property AttributeAllMemberName for attribute hierarchies and
property AllMemberName for user hierarchies). But I can’t find a place where
I could change the name of all level. It’s name is now “(All)”. But I need
to change it. How can I do it with AMO or with BI Development studio? Please
help me.
Thanks for the answer
Dorota
you can't rename it
you want to change it to which value?
"Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
message news:1B0E4BDC-BD75-4483-AB51-24F7747C87FF@.microsoft.com...
> Hi,
> I’m creating dimension with SSAS 2005 – Business Intelligence Development
> Studio. Dimension has attribute hierarchies and user hierarchies.
> For attribute hierarchies and user hierarchies I can define my own all
> member name ( property AttributeAllMemberName for attribute hierarchies
> and
> property AllMemberName for user hierarchies). But I can’t find a place
> where
> I could change the name of all level. It’s name is now “(All)”. But I
> need
> to change it. How can I do it with AMO or with BI Development studio?
> Please
> help me.
> Thanks for the answer
> Dorota
>
|||Hi,
Thank you very much for the answer. I’d like to rename it to polish name,
for example instead of “(All)” I need “Calosc”.
Thanks for the help.
Dorota Papiernik
"Jeje" wrote:
[vbcol=seagreen]
> you can't rename it
> you want to change it to which value?
> "Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
> message news:1B0E4BDC-BD75-4483-AB51-24F7747C87FF@.microsoft.com...
|||well...
in fact it's never used, and the end user generally never use (All) level
directly.
the end user use the all member name you defined.
I know that few applications display "(All)" in the interface.
But it's not possible to change it today.
Log your request in the connect website as an improvment.
"Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
message news:7F16FE08-B741-4148-93B4-C802467735BF@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thank you very much for the answer. I’d like to rename it to polish name,
> for example instead of “(All)” I need “Calosc”.
> Thanks for the help.
> Dorota Papiernik
>
> "Jeje" wrote:
|||Hi,
In Analysis Services 2000 it was possible to change name of the level (All).
So for polish users I changed it to polish value “Calosc”. I have migrated
database of my customer to Analysis Services 2005, and I have expected that
on Analysis Services 2005 I also can change name of level (All). Now I see it
is not possible. So polish users will have to work with the name “(All)”.
Thank for help and for information once more. As you suggested, I’m going
to log my request in the connect website.
Dorota Papiernik
"Jeje" wrote:
[vbcol=seagreen]
> well...
> in fact it's never used, and the end user generally never use (All) level
> directly.
> the end user use the all member name you defined.
> I know that few applications display "(All)" in the interface.
> But it's not possible to change it today.
> Log your request in the connect website as an improvment.
>
> "Dorota Papiernik" <DorotaPapiernik@.discussions.microsoft.com> wrote in
> message news:7F16FE08-B741-4148-93B4-C802467735BF@.microsoft.com...

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.