OLAP cube - formula length

Hi

dear all,

does anybody know the maximum length for an mdx formula for a calculated member in SSAS?

Kind regards

Frederikm

No hard limit in SSAS 2005.|||

Hi

i find this kind of hard to believe...

I have a formula with over 4000 characters... processing this cube is impossible

the formula is a formula in the form of iif(errorcheck, 0, iif(errorcheck,0... etc

if i use the basic formula without the iif's the cube processes without any problems....

Kind regards

|||There is a syntax error in your formula somewhere, and the error message should give you an idea about location of this error. The syntax error is the real cause of why your cube doesn't deploy, not the length of the formula.|||

Hi

about the max length: is this tied to any service pack version? we are using sp1
also the thing is that there is no error in the formula,
for when we use it in a normal mdx query it works without any problem...
only when in the cube things go wrong

|||The fact that it works inside MDX query means that there is no problem with the length. What is the exact error message you are getting when you are trying to deploy cube with such calculated member ?

OLAP cube - errorhandling

Dear all

i have inherited a SSAS 2k5 cube with a large number of calculated members.

I have a number of problems:

sometimes the calculated amounts are very very small like: 0.00000000000034.

This however, does not play nicely with excell... heximal display and so on. anyone know of a way to show these as being 0?

secondly

I have a number of calculated members who themselves contain calculated measures.

When one of their parents is 0, they resort into div/0 erros, etc

For my current project it is unacceptable that these errors are visible for the end user

is there any way that i can catch all the errors at once? or do in eed to include iif statements in the calculations of each calculated member?

Hi, unfortunately you need to put IIF statements around them. For example direct queries from the cube.

with

member measures.test as

0.000000003

member measures.test2 as

IIF(measures.test<0.00001,0,measures.test)

member measures.test3 as

0

member measures.test4 as

IIF(measures.test3=0,0,measures.test/measures.test3)

select {measures.test2,measures.test4} on 0 from [mycube]

or calculated members on the cube

CREATE MEMBER CURRENTCUBE.[MEASURES].[PercentUnavailable] as

IIF([Measures].[Total Count]=0,0,[Measures].[Total Unavailable])*100/[Measures].[Total Count]),

visible=1;

John

|||

Alternative which will perform better in cases where you have a large number of these is to use the format statement

member measures.test as

0.000000003,format='#.00'

OLAP Connectivity

Hello all,

We have Analysis Services 2000 with an OLAP cube. The cube has a role allowing access to all Windows domain users. From my development workstation we can connect to the cube, using Excel or a HTML page with the Office Web component, logged on as any user. However, from any other computer in the domain, and logged on with the same users, we cannot create a connection; the OLAP server is not visible!

The client computers are all the same: Windows 2000 Pro and the users all belong to the domain.

Why can we not connect? Has anybody ever experienced this sort of problem? We need to solve this by Monday, our deadline to show our OLAP application to the users. I would really appreciate somebody's help.

Thanks in advance,

Jerome Smith

Have all other workstations pivot table services installed, that is the local client that SSAS 2000 requires? It muste be the same version(service pack) as the server.

It is not sufficient with Excel installed on each client, you also need pivot table services.

You can download this add in for Excel: http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

HTH

Thomas Ivarsson

|||

Hello Thomas,

I thought that PTS was included in Office. At least that's what the manual for the MS-2074 course says, and I have always been able to connect to Analysis Servers from Excel and FrontPage, and in different companies.

The download you referred me to is actually not PTS but the Excel Add-In for Analysis Services, which affords enhanced functionality but does not solve the problem.

What I really need to do is connect to the OLAP cube from a HTML page with the OLAP web component, included in Office and also in FrontPage.

Thank you for your time.

Regards,

Jerome Smith

|||

Like I have told you earlier, PTS is not installed by default. Install it on the machines that do not work and see if this helps.

The download of the Excel add in will include PTS.

If you are trying to do a http-connection then it is a different story. Office web components are ActiveX and do not use http by default.

Regards

Thomas Ivarsson

|||

OK Thomas,

I'll try this.

Thanks,

Jerome

|||

Good luck. If this is not the problem return with more information and we can continue with other causes.

Regards

Thomas Ivarsson

|||

Hi Thomas,

Well, actually I managed to deploy the application on almost all client PCs WITHOUT installing PTS. Just as I thought, PTS comes included with Office, so it is almost never necessary to install PTS.

The problem was never solved on some developer's notebooks in the IT department; we couldn't connect to the AS Server, no matter what we did (including installing the Add-In from the link you gave me).

It beats me why. I wish I knew. Any ideas?

Jerome

sql

OLAP Connection in c#

Hello,

I'm trying to connect to my OLAP Cube with a c# project.

This is my code:

private void Page_Load(object sender, System.EventArgs e)

{

ADODB.ConnectionClass conn=new ConnectionClass();

conn.Open(ConfigurationSettings.AppSettings["connection_string"].ToString(),ConfigurationSettings.AppSettings["user_conn"].ToString(),ConfigurationSettings.AppSettings["password_conn"].ToString(),0);

try

{ADOMD.CellsetClass cs=new CellsetClass();

cs.ActiveConnection=conn;

string szMDX="";

szMDX += "SELECT ";

szMDX += "{[Measures].members} ON COLUMNS,";

szMDX += "[CAMPAGNE].[Dim CAMPAGNE].members ON ROWS";

szMDX += " FROM [My Roi]";

cs.Open(szMDX,conn); // here there is an error

}

I've receive this error message:

"Impossible to use this connexion to make this operation, the connexion is closed or invalid in this context".

Can you help me please, because I can't find the problem.

Thanks,

Have you tried changing the provider in the connection string to MSOLAP.1 or MSOLAP.2 and did you verify that either TRUSTED_CONNECTION=YES or INTEGRATED SECURITY=SSPI is included?

Olap and reporting service problem

Hi.
This is an OLAP question.
I have a dimension which is a father-son dimension.
I built the cube in analysis server.
In the analysis browser I can see the dimension fine,
I hant my reporting services report to show that dimension.
The problem is that the report won't show me that dimension in
hierarchy tree (even if I check drill down) but flatten it down.
Does some one know if report service can handle those kind of reports?
Thanks.Hi Nick,
I am having the same problem. Have you managed to get an answer? Would love
to know how to solve this.
Anusha
"nicknack" wrote:
> Hi.
> This is an OLAP question.
> I have a dimension which is a father-son dimension.
> I built the cube in analysis server.
> In the analysis browser I can see the dimension fine,
> I hant my reporting services report to show that dimension.
> The problem is that the report won't show me that dimension in
> hierarchy tree (even if I check drill down) but flatten it down.
> Does some one know if report service can handle those kind of reports?
>
> Thanks.
>|||Hi Anusha,
I did managed to get an answer.
You can't do this!
RS is not really an OLAP tool and it doesn't know how to deal with
parent-child dimension and thats why it flatten down the dimension.
Currently, I saw that there isn't any simple way to solve this.
Sorry...
Anusha =D7=9B=D7=AA=D7=91:
> Hi Nick,
> I am having the same problem. Have you managed to get an answer? Would lo=ve
> to know how to solve this.
> Anusha
> "nicknack" wrote:
> > Hi.
> > This is an OLAP question.
> >
> > I have a dimension which is a father-son dimension.
> > I built the cube in analysis server.
> > In the analysis browser I can see the dimension fine,
> >
> > I hant my reporting services report to show that dimension.
> > The problem is that the report won't show me that dimension in
> > hierarchy tree (even if I check drill down) but flatten it down.
> >
> > Does some one know if report service can handle those kind of reports?
> >
> >
> > Thanks.
> >
> >|||Thanks, At least I can stop pulling my hair out!!!
Anusha
"nicknack" wrote:
> Hi Anusha,
> I did managed to get an answer.
> You can't do this!
> RS is not really an OLAP tool and it doesn't know how to deal with
> parent-child dimension and thats why it flatten down the dimension.
>
> Currently, I saw that there isn't any simple way to solve this.
> Sorry...
> Anusha ×?ת×?:
> > Hi Nick,
> >
> > I am having the same problem. Have you managed to get an answer? Would love
> > to know how to solve this.
> >
> > Anusha
> >
> > "nicknack" wrote:
> >
> > > Hi.
> > > This is an OLAP question.
> > >
> > > I have a dimension which is a father-son dimension.
> > > I built the cube in analysis server.
> > > In the analysis browser I can see the dimension fine,
> > >
> > > I hant my reporting services report to show that dimension.
> > > The problem is that the report won't show me that dimension in
> > > hierarchy tree (even if I check drill down) but flatten it down.
> > >
> > > Does some one know if report service can handle those kind of reports?
> > >
> > >
> > > Thanks.
> > >
> > >
>sql

olap and hierarchy problem

Hi.
This is an OLAP question.

I have a dimension which is a father-son dimension.
I built the cube in analysis server.
In the analysis browser I can see the dimension fine,

I hant my reporting services report to show that dimension.
The problem is that the report won't show me that dimension in hierarchy tree (even if I check drill down) but flatten it down.

Does some one know if report service can handle those kind of reports?

Thanks.I assume the dimension is a parent-child dimension. RS is not an OLAP browser. As you have noticed, it flattens the parent-child dimenion. In this presentation download, you will find two approaches for dealing with parent-child dimensions in RS. The first one uses the RS recursive support. The second bypasses the SSAS 2005 provider in order to use straight MDX to expand the hierarchy. Needless to say, both approaches have their limitations and we can expect the support of parent-child dimensions and UDM in general to improve in future releases.|||Hi Teo,
Thanks for your replay, Its hard to find help and support for Olap and for Olap and SSRS on the net.

Its very disappointing to hear that RS does not support OLAP (and for me, if it can't handle parent-child dimension it can't handle olap).

Looks like we will have to use some other programs for working with cubes and creating reports.

Thanks for your post and I will have a look at the link you supplied to check if by any chance there is a way to work with parent-child with out any work arounds.

Regards,
Roy.|||Just to clarify. I didn't say it doesn't support OLAP. I said that it flattens the parent-child hierachies when converting them to two-dimensional datasets.|||I know you didn't - I did ("and for me, if it can't handle

parent-child dimension it can't handle olap").

I think Its a must when working with olap.

Thank for the reply :)
|||

How would you go about using OLAP with SSRS?

Do you purchase OLAP?

I've seen it used, but I know little to nothing about it.

|||You can build OLAP solutions (cubes) with analysis services and then deploy the cube to the analysis server.

After that you can create a report and as a data source tell him to connect to the cube on the analysis server.

But as you can see, reporting services can really work with olap :(

OLAP Analysis Services: Errors in Encryption Library

Hi!

I am trying to process a cube in analysis services of visual studio
2005, with sql server 2005. The error is:

"Errors in the encryption library: Failed to encrypt sensitive data.
Possibly the encryption key is inaccessible because of improper service
account change."

Thanks in advance,

--Dimitris Doukas

You might have changed the account for your Analysis Server service.

To solve this problem you will have to stop Analysis Server. Remove all the files from the Data folder ( located under installation folder) and then start Analysis Server again.

Create a backup for your existing databases before doing that in case you cannot re-process them.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

It worked. Thanks a lot.

--Dimitris

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!sql

OLAP 2005 Calculated Measure using Field/Dimension

I am building an OLAP Cube off someone else's data model, so i am a bit stuck trying to decision off model decisions already made and being used.

They have a field for Satisfaction which has the value Yes, No, and Blank when no survey result was received.

I need to make a satisfaction percent based on (count of Yes) / (count of Yes + count of No). So i do not count blank when no result was received.

I need the caculated measure to work against all of the dimensions, so i do not want to build such a specific MDX code that it only works with Time, for example.

Help will be extremely Appreciated

*A

If there is a Satisfaction dimension/attribute, with the members above: Yes, No, and Blank, and a cube "count" measure like SurveyCount, then does something like this work:

([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])/

(([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[Yes])

+ ([Measures].[SurveyCount], [Satisafaction].[Satisfaction].[No]))

|||WORKED GREAT AND WAS REALLY EASY TO UNDERSTAND AND CHANGE FOR OTHER USES!!!!!!!

OLAP - EXCEL problem - urgent

Hello,

I have a dimension hierarchy with 2 levels, like this:

A - X

- Y

- Z

B - U

- V

- T

And I have a cube with the dimension above and one measure (sum).

The fact table of this cube has 2 columns (level2,sumvalue) and i have the values:

Level2 SumValue

X 1

Y 2

T 6

When I access this cube from Excel and use multiple selection weird stuff happends.

So, I create a Pivot table, in which i select only X,Y,U:

Level1

Level2

Total

A

X

1

Y

2

A Total

3

Grand Total

3

If i give a refersh to this pivot table i get :

SumValue

Level1

Level2

Total

A

X

1

Y

2

A Total

3

B

6

Grand Total

9

Why i see the B in the pivot table, if i selected only X,Y,U and U has no value in the fact table? I need an urgent answer because end-users use a lot the refresh option and in this case they see wrong values.

I'm using Microsoft Analysis Server build 9.00.2153 (SP1) and Microsoft Office Excel 2003 (11.6560.6568) SP2. I made the test with Microsoft Analysis Server SP2 and same version of Excel and i got the same result.

Any help will be appreciated.

Thanks,

Vulcano

A couple of things to check, that I can think of:

Is "Visual Totals" still enabled after refresh (ie. "Totals include Hidden Items" is disabled) - otherwise, these results would be expected?|||Thanks Deepak for your answer.

The property of the pivot table "Include hidden members in totals" is disable all the time. So no hidden members are included. In my dimension i don't have hidden members.

I run a Profiler and I discovered something strange:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[T] , [Dimension].[Hierarchy].[Level1].[ V ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ T ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

Why the two queries are different? Why in the second query [Dimension].[Hierarchy].[Level1].[ B ] disappears?

Thanks,

Vulcano|||

The member which disappeared in the 2nd query is: [Dimension].[Hierarchy].[Level1].[ V ] , not [Dimension].[Hierarchy].[Level1].[ B ] (though maybe these are place-holder, not real, names?).

When you look at the drop-down list of members selected in the pivot table, do you find that this missing member is now selected? If so, this could be a side-effect of refreshing the pivot table (though I'm not sure why the refresh would change the selected members).

|||

My mistake Deepak, the queries are wrong, they are missing the A, B Members of the dimension. I post again the queries:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] , [Dimension].[Hierarchy].[Level1].[ B ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[ V ], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

And you are right again: when the first pivot is created the X,Y,U members are selected, after the pivot is refresh the U member is not selected anymore ( so the member selected are X,Y,B). Is this the right behaviour, or maybe is a bug?

Thanks,Vulcano

OLAP - Dimension

Hi Folks,

We have problems with our Cube.
One of our dimension has more 64K (64,000) members.

We had tried to use grouping but it does not meet our needs.
Can somebody suggest another way to to resolve this limitation problem besides grouping.
Any comments/suggestion (even redesigning the dimension or fact tables) will be welcome.

Thanks and have a great day.This is a big question, can you post the DDL of the mart/warehouse that includes all dim tables and the fact table? If you are storing measures at the dim level you might be able to store them in the fact.|||Hi There,
i have this strong feeling that you can solve this problme by createing a groups on top of your dimension, but one thing every time you need to rebuild this dimension rather then just by increnmental update, rebuild it every time when you process the cube. becuase i have also got the same problem and i am doing the same.

Thanks
Pawan
Originally posted by REVI
Hi Folks,

We have problems with our Cube.
One of our dimension has more 64K (64,000) members.

We had tried to use grouping but it does not meet our needs.
Can somebody suggest another way to to resolve this limitation problem besides grouping.
Any comments/suggestion (even redesigning the dimension or fact tables) will be welcome.

Thanks and have a great day.|||Hi,

This is the DDL:

CREATE TABLE [dbo].[Dim_FII_Code] (
[FII_Code_SID] [int] NOT NULL ,
[FII_Code_Value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FII_Code_Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FII_Code_Group] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dim_Account] (
[Account_SID] [int] NOT NULL ,
[Account_Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Industry_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Industry_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Start_Date] [datetime] NULL ,
[Account_Term_Date] [datetime] NULL ,
[Account_Country_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Country_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_State_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_State_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_City] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Zip_Code] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HQ_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domestic_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Global_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fact_ID] [int] NULL ,
[Fact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Global_4000_Company_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Type_Group] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Type] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales_Region_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales_Territory_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dim_Date] (
[Date_SID] [int] NOT NULL ,
[Month_SID] [int] NOT NULL ,
[Day] [datetime] NULL ,
[Day_of_Week] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weekday_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fact_Search_FII_Code_Usage] (
[Account_SID] [int] NOT NULL ,
[Date_SID] [int] NOT NULL ,
[FII_Code_SID] [int] NOT NULL ,
[Total_Search_Qty] [int] NULL ,
[No_Headline_Search_Qty] [int] NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

We countering problems with Dim_FII_Code, the members for the
exceed for the fii_code_group. It is more than 64k.

Another thing, can you explain how grouping work.
One data model without grouping and with grouping will help.

Thanks and have a nice day.|||Originally posted by pawan
Hi There,
i have this strong feeling that you can solve this problme by createing a groups on top of your dimension, but one thing every time you need to rebuild this dimension rather then just by increnmental update, rebuild it every time when you process the cube. becuase i have also got the same problem and i am doing the same.

Thanks
Pawan

HI ,

Can you explain how grouping work.
One data model without grouping and with grouping will help.

Thanks and have a nice day.|||Hi There,
what you can do that, create a Hierarchical dimension like Data_created and under that the sub member is your 64k dimension which is Dim_FII_Code. one thing don't take the full date just extracte year out of that date field and create one more field in your table called "Dim_FII_Code" i assume so and update that field with this year value. Then go back to your cube and create a Hierarchical dim as YEAR
by using newly created field which is having year value and sub member is Dim_FII_Code. But make sure that this field is not null.
if you have any ? then do mail to this address.
pawan.bhatnagar@.aut.ac.nz

Thanks
Pawan

[Originally posted by REVI
Hi,

This is the DDL:

CREATE TABLE [dbo].[Dim_FII_Code] (
[FII_Code_SID] [int] NOT NULL ,
[FII_Code_Value] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FII_Code_Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FII_Code_Group] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dim_Account] (
[Account_SID] [int] NOT NULL ,
[Account_Code] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Industry_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Industry_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Start_Date] [datetime] NULL ,
[Account_Term_Date] [datetime] NULL ,
[Account_Country_Code] [nchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Country_Desc] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_State_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_State_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_City] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Zip_Code] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HQ_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Domestic_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Global_Ultimate_DUNS_Number] [nchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fact_ID] [int] NULL ,
[Fact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SIC_Code] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Global_4000_Company_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Type_Group] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account_Type] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales_Region_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sales_Territory_Code] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service_Code] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Service_Desc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Dim_Date] (
[Date_SID] [int] NOT NULL ,
[Month_SID] [int] NOT NULL ,
[Day] [datetime] NULL ,
[Day_of_Week] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weekday_Flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Fact_Search_FII_Code_Usage] (
[Account_SID] [int] NOT NULL ,
[Date_SID] [int] NOT NULL ,
[FII_Code_SID] [int] NOT NULL ,
[Total_Search_Qty] [int] NULL ,
[No_Headline_Search_Qty] [int] NULL ,
[Date_Created] [datetime] NULL ,
[Date_Updated] [datetime] NULL
) ON [PRIMARY]
GO

We countering problems with Dim_FII_Code, the members for the
exceed for the fii_code_group. It is more than 64k.

Another thing, can you explain how grouping work.
One data model without grouping and with grouping will help.

Thanks and have a nice day.sql

offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables .

When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

>

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

offline OLAP Cube +MDX

I will be really very much greatful to one who can help me out as I have been struggling to solve this from 2 months.

I have a .mdb database and I want to craete a pivot from it.The size of the database is around 400 MB so it was suggested to create a cube from this database and then create pivot.The database is populated with TOTAL values for all its class variables .

When I create cube from Excel wizard it again summarises and craetes ALL level and the pivot is completely wrong.Infact I get values in the multiples of 2**classvariables.Is there a way where can I create cube by VBA and eliminate ALL LEVEL for every dimension. I have tried so many codes but nothing is working.Any sample code will be a great help.

Here I am getting Multiple Selections for page field as well .Is there any way to disable them?

The second option could be to populate the Listviews with the dimension values and then create a report in Excel using MDX but its not working as well.

I am relly looking looking forward to you great guys.

Regards

Hi,

create the cube with the Excel-Wizar. Save the to C:\

Then there is a SclicerDDL.txt with the CREATE GLOBAL Cube-Statement.

Remove the All Level

Example:

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [(All)], <-Delete this line
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
)

>

DIMENSION [CUBENAME].[DIM-NAME]
(
LEVEL [L1],
LEVEL [L2],
LEVEL [L3],
LEVEL [L4]
),

Run this edited SlicerDDL.txt in the Analysis Server MDX-Samples and the local cube will not contain the All-Level.

hth

Bernhard Saemmer

|||I only have Excel in my machine.Is it possible to do it by VBA?

Offline Cube Refresh

I am having problems with offline cubes. I am using AS2005 SP1 with all the 6 hotfixes installed. Everything with the cube access is fine and I can use excel to create a local .cub file. My problem comes when I try to refresh the cub file. My understanding is that using the excel spreadsheet which created the cube file hitting the refresh button while the cube is offline should refresh the cub file.

This is where my issues start. I get various messages all pointing to either an OLE DB driver crashing or the .cub file being reported as corrupt.

This doesn't happen every time once in a while with no noticable pattern has anyone else experienced the same problems with refreshing offline cubes?

If you willing to try... Please see if installing CTP of SP2 will solve your problem.

You can install it from http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en

If your problems still persist, contact product support and report your problems.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks for you help I have now tried installing CTP SP2 but I still have an error when updating that says that the local cub file is corrupt however it is fine to use and only gives the error at refresh. I have upgraded to XL2003 SP2|||

The last suggestion I might have for you:

Try rebuilding your local cube using CTP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Offline cube cannot open

I built a cube by Microsoft SQL Server 2005 Analysis Services and deploy into the server. The deployment was successful. I extract the cube by Microsoft Excel 2007 to view data. That also successful. Then I built an offline cube from there. I can successfully create the offline cube. And store into a thumb drive and open the offline cube by another pc through microsoft excel 2007/2003 both. I cannot open the offline cube and got the error as 'This datasource contains no visible tables'... and in another pc, it is asking the log in credentials.

The offline cube is disconnected from the datasource and can open anywhere else right? Then how can I got all these issues? Please help me on this. Thanks.

> The offline cube is disconnected from the datasource and can open anywhere else right?

Yes, the offline cube is disconnected from the relational data source, unless you have ROLAP cubes or partitions.

Adrian

|||Thanks a lot Adrian for your reply. But, how can I deploy the cube without partition? Can you eloborate more on this please... thanks|||

If the storage mode is ROLAP for your cube or a partition or dimension that you will query from Excel, then you will need to have access to the relational data. To solve this, use the MOLAP storage mode. To check what storage mode you have, use either SQL Management Studio or BI Development Studio. With SQL Management Studio, use the properties dialogs to check each dimension, cube and partition. With BI Development Studio, connect to the database in online mode (File -> Open -> Analysis Services Database).

Adrian

|||

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

|||

grmohan68 wrote:

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

Offline cube cannot open

I built a cube by Microsoft SQL Server 2005 Analysis Services and deploy into the server. The deployment was successful. I extract the cube by Microsoft Excel 2007 to view data. That also successful. Then I built an offline cube from there. I can successfully create the offline cube. And store into a thumb drive and open the offline cube by another pc through microsoft excel 2007/2003 both. I cannot open the offline cube and got the error as 'This datasource contains no visible tables'... and in another pc, it is asking the log in credentials.

The offline cube is disconnected from the datasource and can open anywhere else right? Then how can I got all these issues? Please help me on this. Thanks.

> The offline cube is disconnected from the datasource and can open anywhere else right?

Yes, the offline cube is disconnected from the relational data source, unless you have ROLAP cubes or partitions.

Adrian

|||Thanks a lot Adrian for your reply. But, how can I deploy the cube without partition? Can you eloborate more on this please... thanks|||

If the storage mode is ROLAP for your cube or a partition or dimension that you will query from Excel, then you will need to have access to the relational data. To solve this, use the MOLAP storage mode. To check what storage mode you have, use either SQL Management Studio or BI Development Studio. With SQL Management Studio, use the properties dialogs to check each dimension, cube and partition. With BI Development Studio, connect to the database in online mode (File -> Open -> Analysis Services Database).

Adrian

|||

Thanks Adrian for your reply. I checked the partitions in my cube. It is already in MOLAP storage mode. I am not using the ROLAP so far. Any further ideas...?

Office Web Components & Analysis Services

Hi,
We've been asked to enable an external client to access a cube via OWC.
The idea is that if they play about with the cube through the PivotTable
control, this will enable them to define their requirements for a reporting
system to sit on top of the cube.
I've read and implemented the contents of the following document:-
- http://www.microsoft.com/technet/pr...5/httpasws.mspx
I've installed OWC and the SQL data access components on a test machine, and
have been trying to access the cube through a web-page hosting the pivot
table control.
Data access across domains is enabled in IE
The error returned is "The query could not be processed: The HTTP server
returned the following error: Forbidden."
Obviosuly some security setting somewhere needs to be tweaked ... but which
one :/
Access to the web-page hosting the OWC control will be locked down by IP
address.
Bearing all the above, what is the most straightforward means of enabling
access via the OWC PivotTble control?
Thank you for help.does the XMLA virtual directory is hosted on the SSAS server himself or on
another HTTP server?
what is the security mode of the virtual directory? Anonymous, Basic, or
integrated security?
"craig_amtdatatechnologies@.discussions.mi"
< craigamtdatatechnologiesdiscussionsmi@.di
scussions.microsoft.com> wrote in
message news:075C02A4-F2FC-485C-BBFB-63B55EF261C8@.microsoft.com...
> Hi,
> We've been asked to enable an external client to access a cube via OWC.
> The idea is that if they play about with the cube through the PivotTable
> control, this will enable them to define their requirements for a
> reporting
> system to sit on top of the cube.
> I've read and implemented the contents of the following document:-
> - http://www.microsoft.com/technet/pr...5/httpasws.mspx
>
> I've installed OWC and the SQL data access components on a test machine,
> and
> have been trying to access the cube through a web-page hosting the pivot
> table control.
> Data access across domains is enabled in IE
> The error returned is "The query could not be processed: The HTTP server
> returned the following error: Forbidden."
> Obviosuly some security setting somewhere needs to be tweaked ... but
> which
> one :/
> Access to the web-page hosting the OWC control will be locked down by IP
> address.
> Bearing all the above, what is the most straightforward means of enabling
> access via the OWC PivotTble control?
>
> Thank you for help.
>
>