Try running the query on the bottom of this thread http://bidshelper.codeplex.com/workitem/32500 to see if your extended attributes come back.

Yikes. That query (after putting in my specific parameters) does not bring anything back. However, this query I have brings me back a nice display of all the extended properties for which I am looking. Am I out of luck?


With

vDBCatalog as
(select DENSE_RANK() over (Order By O.object_id, s.name ) as TableKey,

   ROW_NUMBER() over (Order By O.object_id, c.column_id) as ColumnKey,
   SchemaName = s.name,
   TableID = O.object_id,
   TableName = O.name,
   TableHasClusteredIx = CASE WHEN i.type = 1 THEN 'Y' ELSE 'N' END,
   ClustIxName = CASE WHEN i.type = 1 THEN i.name ELSE '' END,
   ColumnOrdinal  = c.column_id,
   ColumnName = c.name, 
   TableType = O.type,
   OrdinalPosition       =        c.column_id,
   DataType   = st.name,
   Nullable   = c.is_nullable,
   IsIdentity = c.is_identity,
   MaxLngth   = c.max_length,
   MaxPrecision  = c.precision,
   MaxScale      = c.scale

from sys.objects o
join sys.schemas s on (O.schema_id = s.schema_id)
join sys.tables t on (o.object_id = t.object_id)
left join sys.indexes i on (t.object_id = i.object_id)
join sys.columns c on (o.object_id = c.object_id)
join sys.types st on (c.system_type_id = st.system_type_id)
where o.type = 'U' and

(i.type = 1 or i.type is null)),

Descr as
(select * from sys.extended_properties where name = 'Description'),
Example as
(select * from sys.extended_properties where name = 'Example Values'),
SSAS as
(select * from sys.extended_properties where name = 'Display Folder'),
ETL as
(select * from sys.extended_properties where name = 'ETL Rules'),
TblDesc as
(select * from sys.extended_properties where name = 'Table Description')

select distinct TOP 100 PERCENT
c.SchemaName,
c.TableName,
TableDescr = ISNULL(TD.value, ''),
c.TableType,
c.ColumnName,
c.ColumnOrdinal as ColumnOrder,
BizDescription = D.value,
ExampleValues = ISNULL(E.value, ''),
ETLRules = ISNULL(ETL.value, ''),
SSASFolderName = ISNULL(SSAS.value, ''),
c.DataType,
c.IsIdentity,
c.MaxLngth,
c.MaxPrecision,
C.MaxScale,
c.Nullable,
c.TableHasClusteredIx
from vDBCatalog c
left join Descr D on c.TableID = D.major_id and c.ColumnOrdinal = D.minor_id
left join Example E on c.TableID = E.major_id and c.ColumnOrdinal = E.minor_id
left join SSAS on c.TableID = SSAS.major_id and c.ColumnOrdinal = SSAS.minor_id
left join ETL on c.TableID = ETL.major_id and c.ColumnOrdinal = ETL.minor_id
left join TblDesc TD on c.TableID = TD.major_id

ORDER BY 1, 2, ColumnOrdinal

What do you have to change in the query at the bottom of http://bidshelper.codeplex.com/workitem/32500 in order to make it return your extended properties? I wonder if it's something small like removing "and p.class=1"? That's the only thing I spot after 5 minutes of studying your query. If you can figure out how your extended properties are stored slightly differently than we're expecting, then we will consider changing the code to make it more flexible so it meets your needs (and everyone else's needs)

I'll check a few things.

1. Why my catalog is not bringing back data from your query.
2. How my catalog differs from what your code expects.
   

The initial population of the extended properties in our database was achieved by using the dynamic sql generated by the Kimball 2008R2 data modeling spreadsheet excel macro.

I stand corrected!

Properties DO come back when I run your query. So, back to my original question. Any idea why extended properties only commit at the table level and not the column level?

Am I doing the right thing by right clicking on the dimension and selecting "sync descriptions"; or, should I be doing something else?

I don't know why it's not working. With another person who was using the Kimball spreadsheet, they had their extended properties on the underlying table, but the dimensions were connected to views on top of the table. BIDS Helper was looking for extended properties on the views.

You might run Profiler connected to SQL Server, watch what queries BIDS Helper runs looking for extended properties, then see if that helps you troubleshoot what the problem is.

One other comment... if you have a dimension attribute where you have column A as the KeyColumn and column B as the NameColumn, it will look for the extended properties on column B.

If you have a dimension attribute with column A as the KeyColumn and no NameColumn specified, it will look for the extended properties on column A.

I disabled the views from being written to the dB.

Anyway, this makes sense. I got some of the attribute descriptions to cascade; however, I'm noticing that if I select multiple ADDITIONAL descriptions, ONLY THE LAST ADDITONAL extended property is showing. So, if I select "ETL Rules", "Table Type", and "Used In Models" in the dialog box of this utility, only the value selected in the drop down list and "Used In Models" show up in the "description" property of the SSAS objects.

Very odd. If you need a visual let me know. In the meantime, I will be going through my metadata with a fine-toothed comb.

BTW, the extended property names generated by the Kimball Spreadsheet macro, at the Table Level and Column Levels, are NOT always the same. For example, the value "Table Description" appears in the Table Level Extended Props as one of the "name" properties, and "Description" appears in the column level extended props as one of the "name" properties.

Ran the trace. Everything is being picked up by the database queries correctly.

For some reason, only 2 extended properties are making it into the descriptions:

1. The primary extended property in the drop down list.
2. The LAST CHOICE SELECTED FROM THE CHECKBOXES (no going from up to down, but rather the ORDER IN WHICH I PERFORM THE CHECKS).
   

So, if "description" is in the drop-down box, and I select the "ETL Rules" checkbox then the "Comments" checkbox, the final description property shows only "Descripition" and "Comments"

Any thoughts?

Great job! You just figured out the bug. Thank you so much.

The code was using .SelectedItems and it should have been using .CheckedItems. I've checked in the fix for that and the fix will be in the next release.

If you need a beta build including this fix, click on furmangg and contact me and I'll get it to you.

Thanks again!

Great job! You just figured out the bug. Thank you so much.

The code was using .SelectedItems and it should have been using .CheckedItems. I've checked in the fix for that and the fix will be in the next release.

If you need a beta build including this fix, click on furmangg and contact me and I'll get it to you.

Thanks again!

I don't think this fix made the migration. Either that or I downloaded the wrong version of BIDS HELPER :(