Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!

Dimension Design using attributes?

I would like to design a dimension as part of a cube to enable all survey results to be displayed in a single excel chart.

I have 10 questions each with answer choices (A, B, C, D, NO Response)

The excel chart would have a bar for each question, and each bar would be composed of 4 colors representing either A,B,C or D.

I can generate 10 dimensions (one for each question), but I can't get it to fit in the same chart. I would like to create one dimension encapsulating the entire survey..by possibly using attributes.

Is there a bettwer way to do this...using excel and as2005?

Any help or advise would be appreciated!

The "Survey" section of this paper on many-to-many dimensional modelling discusses how to incorporate the answers to multiple questions of a survey in a single dimension:

http://www.sqlbi.eu/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf

>>

...

Survey

The survey scenario is a common example of a more general case where you have a lot of attributes associated to a case (one customer, one product, and so on) and you want to normalize the model because you do not want to change the UDM each time you add a new attribute to data (as adding a new dimension or changing an existing one). One common scenario is a questionnaire consisting of questions that have predefined answers with both simple and multiple choices.

...

>>

Dimension [] and Measure [] have no relation in Excel Addin

Hi,

I am kind of new to SSAS 2005 but done some work on sql 2000 MSOLAP. There is a difference I spotted between the product which is annoying me and not sure if there is a solution for the same.

In SQL 2000 MSOLAP i created one cube which had only is related to active outlets listing of company only related to one dimension.

I added this to virtual cube which had many other cubes and many other dimensions. Based on virtual cubes I created reports and caculated measures using the active outlet listing without having to worry about it being linked to other dimensions. It always used to generate data in excel addin where many dimensions where put in page and row sections.

Now in SSAS I created it as part of standard cube related only to one dimension but if use it excel addin with other dimensions it gives me an error saying the Dimension [Year] and Measure [Active outlet listing] has no relationship.

How do I handle the above. Any quick help will be appreciated.

Thanks & Regards

Ramnath

This issue was discussed some time back in Chris Webb's blog:

"I did spot a problem that I've seen in other AS2005-enabled clients and which I hope won't turn into a trend. I found it by creating a report using Adventure Works, putting the measure [Internet Sales Amount] on columns and trying to put something from the Geography dimension on pages, which resulted in a dialog box informing me that the Geography dimension was unrelated to the [Internet Sales] measure group and stopping me from completing the operation. Now in 99% of cubes this would be a good thing to do, but I've already built a few cubes where I have dimensions that have no relation to any measure group but where selections on them do impact calculations (think solving the start/end date problem, where you might want to create an end date dimension with no relation to a measure group); of course, this client feature stops you from being able to design cubes in this way."

And there was this comment suggesting a work-around, if it helps you:

Mosha

"In the meantime, the workaround would be to create calculated measure which redirects to the physical measure ?"

December 29 12:10 AM
(http://www.mosha.com/msolap)

|||

Thanks Deepak,

I actually tried the work around before reporting this as a problem. The work around becomes very cumbersome when we have large number for hierarchies for one dimension.

I found the easier way by creating associations for all measures groups to all dimensions. The association is at the highlest level in all dimension structures.

How can we report this issue to Microsoft and get help in fixing it in the Excel addin. Does any body know if there is going to be maintenance release for this Excel addin.

The Excel addin link page also does not work so people are only able to see the FAQ section and download section of this addin. Other details of this addin are not available.

Thanks For all the support.

Digits converted to null

Hi,
Im loading data from excel source into a table with all the columns as varchar, I found out that rows from excel with digit value are transformed to Null values into the destination table.

One workaround was to add single quote at the beginning of the digits from the excel file. Is there a way in the SSIS to do the transformation instead ofmanually updating the excel file?

any help...tnx..

Not that I know of and I've spent some time looking. Reading data from Excel is tricky. For example, the data type of the column can change from row-to-row, and Excel can store data that doesn't match the defined data type. These are pretty big challenges for an OLE DB provider trying to read it like a table.
In several cases I've resorted to exporting my Excel source to a tab-delimited file for SSIS to read. At least you can automate this instead of having to manually fix each Excel file.|||You need to specify Import Mode by adding IMEX=1 to the connection string, in the Extended Properties argument along with the Excel version and HDR name/value pairs.

Please note that we have done our best to document this and other known issues in the topics for the Excel Source and the Excel Destination in BOL. This content has been further augmented for the upcoming Web refresh of BOL.

-Doug|||This type of problem was also applicable to DTS so this article should

probably still apply

Excel Inserts Null Values

(http://www.sqldts.com/default.aspx?254)

Allan

"DouglasL@.discussions.microsoft.com"

wrote in message

news:3b274106-b020-42bb-95cf-a1aeb554ea98@.discussions.microsoft.com:

> You need to specify Import Mode by adding IMEX=1 to the connection

> string, in the Extended Properties argument along with the Excel version

> and HDR name/value pairs.

>

> Please note that we have done our best to document this and other known

> issues in the topics for the Excel Source and the Excel Destination in

> BOL. This content has been further augmented for the upcoming Web

> refresh of BOL.

>

> -Doug

different character when exported to excel..HELP

Hi everyone!

Good day...I have this report which has records with tabs/spaces on it, after I export it to excel the tabs/spaces are converted to squares/blocks. I would like to know if there is a work around on this without changing the record itself..I need to eliminate the squares on some of the record items... please help... Thanks a lot..

JK

For all expressions you can modify the value in the report inself.

By default an expression will be like: =Fields!datavalue1.Value
Now you can replace all your tabs by adding .Replace(vbTab, " ") to the expression.

|||

hi,

thanks for your help but i dnt know where to add the statement to replace tabs... would it be

=Fields!datavalue.Value & Replace(vbTab, " " )

if wrong kindly correct it please thank you very much...

JK

|||

All expressions in Reporting Service are VB.NET code, so the syntax would be:
=Fields!datavalue.Value.Replace(vbTab, " ")

For more information: http://msdn2.microsoft.com/en-us/library/system.string.replace.aspx

|||Thanks Jan Peter to your help..ill try it if it works...god bless..