Losing dimension permissions in security role with deployment wizard

I'm working with an AS database that has security roles configured with dimension and dimension data permissions. If I deploy my BI project using Deployment Wizard with the option to Retain roles and members, the roles and members do remain intact but the cube access in the roles is set to None and the dimension and data dimension permissions are gone.

Is there any way to use Deployment Wizard so that it retains the roles, members and the cube/dimension permissions defined for the role? I don't want to deploy roles and retain members because my roles will be different depending on the target server.

Thanks in advance.

This seems to be the same bug as described in Chris Webb's blog today:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!978.entry

>>

Deployment Wizard Bug

The Deployment Wizard is meant to allow you to deploy a project from BIDS but not overwrite certain parts of the associated Analysis Services database. Examples of things you’d not want to overwrite on the server include partitions, connection strings and security settings. However once I started actually using this functionality with my customers over the last few months I came across (or rather was informed of by one of said customers and was able to repro with others) a pretty major bug: when you select the ‘Retain Roles and Members’ option to not overwrite any of the security roles, while it does indeed not overwrite any of the roles it sets cube access in all of them to None and this in turn loses all the dimension security settings defined on the roles. So, be warned… it’s with PSS at the moment but I’m told it probably won’t be fixed in SP2.

>>

|||Yes, I am probably one of the customers Chris references in his blog.

Lookup current id in Kimball Type II dimension for fact rows

Hi all,

Maybe someone here can help me out: I have a Kimball type II dimension, where i track changes in a hierarchy. Each row has a RowStartDate and RowEndDate property to indicate from when to when a certain row should be used.

Now i want to load facts to that table. So each fact will have a certain date associated with it that i can use to lookup the right Id (a certain SourceId can have mulitiple integer Ids when there are historic changes) and then load the facts.

Is there a building block I can use for that? I could do this with SQL scripts but the client would prefer to have as much as possible done in SSIS. The Lookup transformation will only let me specify an equal (inner join where A=B) join, but i need equal for one column (SourceId) and then >= and <= (RowStart and RowEnd) to find the right row version.

Any ideas? Thanks in advance,

John

That question have been asked before. There are several works arround to that problem, here is one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406485&SiteID=1

You may want to try a search with 'SSIS range lookup'

|||

That's it, excellent! I was googling on the wrong words.

Thanks,

John

Looking for recommended approach to merging records

I am trying to create a dimension table and I am pulling in data from two tables to create it. I need all records from table A, any records from table B that are not in table A, and I need to use the fields from B for those records that do match. What would be the best way to approach this, merge join + derived columns, union all + aggrigation? Any suggestions?

It seems like it's harder to do this in ssis rather then just doing it in the database.

If you think it is easierr to do it in the database then use the database. It is an oft made assumption that if you're using SSIS then you should use it to do EVERYTHING. That's not the case - database engines do what they do fantastically well so if you can use it either by:

Writing SQL in an OLE DB Source