Browsing SSAS (2005) Dimensions from Excel

Hi all,

I am having difficulty understanding why my dimension names aren't showing up in a pivot table field list when I browse SSAS 2005 cubes from Excel 2003 (using the 90 PTS OLAP DLL).

Has anybody else experienced this problem where the dimension names aren't being made visible? Unfortunately all I see are my attribute names and these, by themselves, are extremely ambiguous.

Just wondering if there are any quick and easy fixes or work-arounds that anybody could recommend.

Thanks in advance,
DB
Since the hierarchies within dimensions (including the visible default attribute hierarchies) are probably what you're seeing in Excel, one work-around would be to create user-defined hierarchies with more meaningful names within the dimensions, even if they merely mimic an attribute hierarchy with an ambiguous name. The corresponding attribute hierarchies could then be hidden, to avoid confusion.

http://msdn2.microsoft.com/en-us/library/ms175631(en-US,SQL.90).aspx
>>

Configuring Multilevel Hierarchies

The Cube Wizard and the Dimension Wizard in Business Intelligence Development Studio in Microsoft SQL Server 2005 Analysis Services (SSAS) create multilevel hierarchies that are based on natural (one-to-many) relationships between columns in tables of a database. You can also create and edit hierarchies by using the Dimension Structure tab of Dimension Designer.

The Hierarchies and Levels pane of the Dimension Structure tab shows the hierarchies in the dimension. The title bar for a hierarchy shows the name of the hierarchy, which you can change by setting the Name property of the hierarchy. The levels of a hierarchy are marked by dots—the root level is marked by a single dot, and each level below the root is marked with an additional dot.

Click a hierarchy to display its properties in the Properties window. Click a level in the hierarchy to display its properties.

Creating Multilevel Hierarchies

Create multilevel hierarchies by dragging attributes from the Attributes pane of the Dimension Structure tab to a blank area on the Hierarchies and Levels pane. You can also drag columns from the Data Source View pane to the Hierarchies and Levels pane. Dragging a column creates the corresponding attribute, if the attribute does not already exist. To add an attribute or a column as a level in an existing hierarchy, drag the attribute or column to the existing hierarchy. As you drag an attribute or a column over a hierarchy, a bar indicates where the new level will be created when you drop the attribute or the column on the hierarchy.
>>

http://msdn2.microsoft.com/en-us/library/ms174939(en-US,SQL.90).aspx
>>
Showing or Hiding an Attribute Hierarchy

If you want to add an attribute as a level to multilevel hierarchies, but you do not want users to see the attribute hierarchy in client applications, set AttributeHierarchyEnabled to True and set AttributeHierarchyVisible to False. These settings prevent users from browsing an attribute hierarchy without disabling it. The AttributeHierarchyVisible setting is ignored if AttributeHierarchyEnabled is set to False.
>>

|||Your work-around makes sense. But it is a great deal of effort to do this for all of our dimension attributes in all of our dimensions.

I am still somewhat amazed that Excel 2003 isn't automatically displaying dimension names. Call me crazy but it seems to me that the name of the dimension is significant enough to display in some fashion.

Thanks,
db

browsin from excel

i can connect olap cubes from excel on any client with blank username and password, although i defined roles and users, a user who skips username and password fields can see all cubes wity any browsing tool ( Proclarity, excel etc )

thx in advance

The username and password field in Excel is only used if you have setup HTTP authentication. Otherwise windows integrated security is used. This means that you must have these users or a group that they are members of in a server or database role that has permission to browse the cubes.

Breaking a dataset into chunks

Hey everyone. I'm trying to recreate some Excel reports my company has
been issuing for the last few years using Reporting Services (data is
coming from an Analysis Services cube now). The reports display
information over a retail hierarchy. Here's what I'm trying to do for
one of them: On one report issued for every entity that has children,
there are five columns of space allotted for key measures on each of
the children (and the report needs to expand to the necessary number
of pages for all children to be displayed, up to five per page). Then,
immediately next to it on each page, the same information about the
current entity and its hierarchy parents must be put in on each page.
There would be up to four columns here. At first I tried fitting this
into two matrix controls, but I quickly realized that SSRS will
process the first matrix for all records in its dataset before getting
to the second one, forcing it to start displaying it no sooner than
the last page of the first matrix. If anyone knows a brilliant way to
allow two unlike-sized datasets to be used in two matrices that can
interrupt each other to get data onto a page, by all means let me know
(I'm not holding my breath for that one though).
This brings me to my actual question. The next solution we've come up
with, as much as we don't like it, is assuming a maximum of 25
children for any entity and creating five separate pages, each one
with a matrix that pulls in a maximum of five records. This of course
probably means five datasets, unless I can set up filtering to limit
which rows from the dataset are being used for each control. So, my
question is this: can anyone suggest a good way to either A) break the
dataset into chunks of <=5 records, or B) filter the each page's
control so that it only takes the appropriate <=5 records, without
redisplaying a record that was already displayed on a previous page?
Any help would be MUCH appreciated. Thanks!On Jan 22, 11:41=A0am, adam.schm...@.gmail.com wrote:
> If anyone knows a brilliant way to
> allow two unlike-sized datasets to be used in two matrices that can
> interrupt each other to get data onto a page, by all means let me know
> (I'm not holding my breath for that one though).
If I understand your problem, you could: Put each object into a
Rectangle object, then align the Rectangles to have the same Top edge,
and align the Right edge of the left rectangle to meet the Left edge
of the right rectangle.
> So, my
> question is this: can anyone suggest a good way to either A) break the
> dataset into chunks of <=3D5 records,
If your source data is a SQL Server, then try using the ROW_Number()
OVER ( ORDER BY FIELD ) function in the statement to generate row
numbers. Then, you can group by ( RowNumber - RowNumber Mod 5 ) to
create groups of 5.
> or B) filter the each page's
> control so that it only takes the appropriate <=3D5 records, without
> redisplaying a record that was already displayed on a previous page?
You could create a group as defined in (A), then put the Group in a
List object, then go to the List's properties and check the Page Break
at End. Then put all your display stuff inside the List. There are
probably other ways of doing this.
-- Scott|||Thanks Scott! I got as far as the two rectangles idea and it almost
worked -- there was only one problem. I need to have the same four
columns of information on each page in the right matrix, whereas the
left matrix just needs to expand to the size of the dataset. This is
so that for example a district manager could look at each attribute on
any page of five stores (from the left matrix) and compare it to his
entire district, his districts region, the rest of the stores in the
same brand division, and the entire company's performance (in the
right matrix). Again, five or less stores per page. Since these are
coming from different data sets, I'm having issues getting the two
data sets to repeat the same amount of times and getting the page
breaks to happen in the right places.
This provided a great jumping-off point for me, though, and I have a
hack solution that will work, although it's not great. The second
matrix can simply be replaced by four columns of textboxes, each one
displaying a specific data point. Instead of the "parents" dataset
containing up to four records, I'll need to use four datasets, each
containing up to one. Then the textboxes can display the first value
in each of those datasets for the appropriate field. Each column of
textboxes (or all four) can then be nested in a rectangle and the
rectangle can be set to repeat with the first matrix.
Obviously, this is labor-intensive, and kind of a pain, especially
given some of the formatting constraints that are going to come up.
So... if anyone knows a way to circumvent using 4 columns, 25 text
boxes each, that still allows me to set the RepeatWith property to the
left matrix, I'm all ears.
Thanks!
On Jan 22, 11:00 am, Orne <polysilly...@.yahoo.com> wrote:
> On Jan 22, 11:41 am, adam.schm...@.gmail.com wrote:
> > If anyone knows a brilliant way to
> > allow two unlike-sized datasets to be used in two matrices that can
> > interrupt each other to get data onto a page, by all means let me know
> > (I'm not holding my breath for that one though).
> If I understand your problem, you could: Put each object into a
> Rectangle object, then align the Rectangles to have the same Top edge,
> and align the Right edge of the left rectangle to meet the Left edge
> of the right rectangle.
> > So, my
> > question is this: can anyone suggest a good way to either A) break the
> > dataset into chunks of <=5 records,
> If your source data is a SQL Server, then try using the ROW_Number()
> OVER ( ORDER BY FIELD ) function in the statement to generate row
> numbers. Then, you can group by ( RowNumber - RowNumber Mod 5 ) to
> create groups of 5.
> > or B) filter the each page's
> > control so that it only takes the appropriate <=5 records, without
> > redisplaying a record that was already displayed on a previous page?
> You could create a group as defined in (A), then put the Group in a
> List object, then go to the List's properties and check the Page Break
> at End. Then put all your display stuff inside the List. There are
> probably other ways of doing this.
> -- Scott