Create a Calculated Member in existing Cube

Question

Post reply

Create a Calculated Member in existing Cube

Agson Chellakudam

Ten Centuries

Points: 1387
More actions
August 27, 2008 at 12:45 am

#189763

Hi All,
I am trying to create a calculated member on the deployed OLAP Cube.
I used 'Alter Cube CubeName Create Member NewCalcuatedMember ...' command and but it creating the member for my session only.
I need to reflect the changes on the deployed cube so that others can also use it.
Any alternatives?
Regards,
Agson

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Is there a reason you are not adding it to the calculations tab in BIDS?

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 2

Thanks for your reply.

I may not be able to use BIDS here.. We need to customize OR chage the calculated members during / after the cube deployment.

Fro Ex: Dynamic Time Period Restrictions. Editing the named SET etc.

So I thought of providing an UI Interface to the power user to do so.

I am trying to use AMO and implement the same. I got a reply saying MDX can't be used in this context

and XMLA / AMO can provide the solution.

I will post the solution once it is tried.

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 3

Hi,

I did the calculated measure changes, new claculated members usign AMO.

I tried using Script task of the SSIS 🙂

Please find the sample code to edit a named SET in the deployed Cube using AMO.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

'

' Add your code here

Dim ServerObj As Server

Dim CubeDatabase As Database

Dim SelectedCube As Cube

Dim MDXCommand As Command

Dim DataSource As String

Dim commandObj As Command

Dim StrtoReplace As String

Dim StrforReplace As String

Dim MDXScriptObj As MdxScript

ServerObj = New Server

DataSource = "Data Source=" + Dts.Variables("OlapServer").Value.ToString()

ServerObj.Connect(DataSource)

CubeDatabase = ServerObj.Databases.GetByName(Dts.Variables("OlapDb").Value.ToString())

'Changing the LastPeriods value based on the DTS variable. Looping through all the cubes in Olap Db

For Each SelectedCube In CubeDatabase.Cubes

'Retrieve MDXScript Command used for Calculated measures

MDXCommand = SelectedCube.MdxScripts.Item(0).Commands.Item(0)

StrtoReplace = MDXCommand.Text.Substring(MDXCommand.Text.IndexOf("CREATE SET CURRENTCUBE.[Scorecard Period]"), (MDXCommand.Text.IndexOf(";", MDXCommand.Text.IndexOf("CREATE SET CURRENTCUBE.[Scorecard Period]")) - MDXCommand.Text.IndexOf("CREATE SET CURRENTCUBE.[Scorecard Period]")) + 1)

StrforReplace = StrtoReplace

StrforReplace = StrforReplace.Replace(StrforReplace.Substring(StrforReplace.IndexOf("LASTPERIODS("), (StrforReplace.IndexOf(",", StrforReplace.IndexOf("LASTPERIODS(")) - StrforReplace.IndexOf("LASTPERIODS(")) + 1), "LASTPERIODS(" + Dts.Variables("LastPeriodMonths").Value.ToString() + ",")

MDXCommand.Text = MDXCommand.Text.Replace(StrtoReplace, StrforReplace)

SelectedCube.MdxScripts.Item(0).Update()

SelectedCube.Update()

Next

End Sub

Note: We can append the new Calculated Measure MDX to the MDXCommand, so that new measure will be created.