exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?
Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:

> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
> and sysdiagrams as user tables, and there are no columns in either of these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studio
> do it?
|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?
|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...[vbcol=seagreen]
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:

exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:
> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
> and sysdiagrams as user tables, and there are no columns in either of these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studio
> do it?|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:
>> I want to get a list of user tables that excludes dtproperties and
>> sysdiagrams. In Management Studio these two tables show up in their own
>> Tables > System Tables folder.
>> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
>> dtproperties
>> and sysdiagrams as user tables, and there are no columns in either of
>> these
>> views to differentiate these tables from true user tables.
>> Is there any way to exclude these two annoying tables using selection
>> criteria in any of the standard metadata views? How does Management
>> Studio
>> do it?

exclude system tables in metadata

I want to get a list of user tables that excludes dtproperties and
sysdiagrams. In Management Studio these two tables show up in their own
Tables > System Tables folder.
However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtproperties
and sysdiagrams as user tables, and there are no columns in either of these
views to differentiate these tables from true user tables.
Is there any way to exclude these two annoying tables using selection
criteria in any of the standard metadata views? How does Management Studio
do it?Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never mind...
"Dana" wrote:

> I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include dtpropertie
s
> and sysdiagrams as user tables, and there are no columns in either of thes
e
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management Studi
o
> do it?|||Dana,
If you don't use SQL Server to maintain ER diagrams I believe you can delete
both tables without consequence. Otherwise, excluding them in the predicate
is the only way I know of.
-- Bill
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:B7ACAF22-6FAE-4364-B4C3-5673CCFC9AE0@.microsoft.com...
>I want to get a list of user tables that excludes dtproperties and
> sysdiagrams. In Management Studio these two tables show up in their own
> Tables > System Tables folder.
> However, both sys.tables and INFORMATION_SCHEMA.TABLES include
> dtproperties
> and sysdiagrams as user tables, and there are no columns in either of
> these
> views to differentiate these tables from true user tables.
> Is there any way to exclude these two annoying tables using selection
> criteria in any of the standard metadata views? How does Management
> Studio
> do it?|||Good to know, Dana.
"Dana" <Dana@.discussions.microsoft.com> wrote in message
news:8162BD94-2F40-4D31-BCE9-0D3BB27AD3E1@.microsoft.com...[vbcol=seagreen]
> Oops, just saw sys.tables.is_ms_shipped = 1 for these guys. Never
> mind...
> "Dana" wrote:
>

Excel Worksheets Become Corrupt -- Bad Metadata?

One of my main users has had a few Office 2003 SP2 worksheets go corrupt on him and I can't seem to figure out why.

For performance reasons, I recommended that he use page filters whenever possible. As of late, he'll send a workbook along that has a few page filters (between 3 and 4) and one of the page filters gets mixed up somehow.

For example, let's say he has a Product Line page filter and a Product Name page filter. The Product Line page filter has multiple selections enabled and he will go ahead and select a few product lines and save the file so he can simply refresh the data in the future. Eventually, the Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable, usually resulting in a strange error indicating that Excel cannot complete the task with available resources when the drop-down is selected. The other page filters seem to behave normally and the report will still operate until the corrupted page filter(s) are selected.

I've seen 2 of his worksheet/workbooks exhibit this behavior this week. He says he's encountered this before and has rebuilt these reports from scratch only to end up with the same issue.

The AS2005 cube has not undergone any recent changes.

Any thoughts?

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

|||

Tigran.Hayrapetyan wrote:

<quote>Product Name filter will actually contain the Product Lines and the Product Line filter becomes unusable...</quote>

This is something strange, and it would be good for you to provide more details here. One idea that crosses my mind: is it possible that some members from Product Line attribute/hierarchy have the same unique name as some members from Product Name attribute/hierarchy? If that is the case, you probably should modify the naming scheme in your SSAS cube to always include hierarchy name in member unique name.

Thanks for the reply Tigran. What are some other details that I can provide?

As for the same names, the values for Product Name and Product Line are vastly different from one another.|||

What I would like to know is what exactly happens in Page filter. When you use the dropdown for Product Name hierarchy, do members from Product Lines hierarchy appear in the Tree control?

But whatever the case, I think the best solution could be to involve Microsoft Support in resolving this issue.

|||

I came accross similar issues and found that the most easiest way to solve this is to use the Excel pivot table wizard to remove all the offending dimensions from the pivot view, refresh the pivot and put back the dimensions in the pivot view.

This will happen mostly when you change dimensions names or when the dimensions member names content change while the client has set a selection on a given member. (no longer in the cube).

Also be carefull with ColumnKeys and ColumNames It lloks like Excel will choke on dimensions where you defined a name and a key column while it was initially only a key column instead of using both a name and a key,

Excel will now use a key (internally, hard to spot). This is mostly impacting Excel views where you use VBA to control the pivot.

Hope it helps a little bit.

So, on the same topic, the issue I am facing now is linked to default member of a time dimension to be the current date.

Initially it works, however, as soon as I update the cube, it will stop to work because the current date set in the Excel view is no longer present in the cube as the current date set as default in the cube. Excel will not pick-up these changes dynamically without removing and putting back-in the dimension. (Excel2003 PTS9)

This lack of dynamic refresh of Excel members is annoying and was already an annoyance in SQL2000.

I would rather have the selector set-back to "ALL" rather than chocking on a missing member.

End users can cope with a selector reset to all, they cannot cope with a cube returning cryptic error messages.

Philippe.

|||

One more comment.

When using VBA to pilot a pivot from code, if you attempt to select a member that is no longer in the cube, you may end-up corrupting your pivot table.

I suspect tha this can also happen in some other cases where you do not use VBA.

Symptoms are as follow:

- Calculated members returns #value error code

- Break-down by individual members are no longer correct while the total remain correct.

Only way out is to rebuild a new pivot table from scratch.

This was happening a lot with Excel XP, it still happen with Excel2003 but very unfrequently.

Just to be on the safe side, when I am done developing a cube and its Excel pivot front-end, I always re-create the pivot from scratch just to make sure it is as clean as it can get.

Philippe