We've got a database which contains encrypted data. To encrypt and decrypt
the data we use 3DES algorithm which is encapsulated into a C# assembly. How
do we incorporate this algorithm into SSAS? We SSAS as the backend for the
reporting services so it's important that reporting services sees the data
in decrypted.

Thanks,
Gilgamesh

On Nov 7, 4:04 pm, "Gilgamesh" <gilgamesh4e...@aol.com> wrote:
> We've got a database which contains encrypted data. To encrypt and decrypt
> the data we use 3DES algorithm which is encapsulated into a C# assembly. How
> do we incorporate this algorithm into SSAS? We SSAS as the backend for the
> reporting services so it's important that reporting services sees the data
> in decrypted.
>
> Thanks,
> Gilgamesh

I found this in the SSAS documentation:

To this end, Analysis Services provides the ability to add assemblies
to an Analysis Services instance or database. Assemblies allow you to
create external, user-defined functions using any Common Language
Runtime (CLR) language, such as Microsoft Visual Basic .NET or
Microsoft Visual C# .NET. You can also use Component Object Model
(COM) automation languages such as Microsoft Visual Basic or Microsoft
Visual C++.

Assemblies let you extend the business functionality of MDX and DMX.
You build the functionality you want into a library, such as a dynamic
link library (DLL) and add the library as an assembly to an instance
of Analysis Services or to an Analysis Services database. The public
methods in the library are then exposed as user-defined functions to
MDX and DMX expressions, procedures, calculations, actions, and client
applications.


However, I doubt that answers your question. Can you elaborate a bit
on the issue or provide an example? I can certainly understand
encrypting sensitive data in an OLTP database (e.g. financial or
medical information) but a core aspect of a data warehouse is the
ability to aggregate measures. If the measures are encrypted I don't
know if OLAP will provide any performance incentive.

That's how we handle encryption/decryption in the database. From within
stored procedures we call user-defined functions, which are basically public
methods in the assembly. The requirement is to have data encrypted at trest.
We were thinking that if we could implement a similar pattern in SSAS that
way we could utilize the same encryption algorithm. Bottom line, we need to
have certian columns encrypted.
-G

"happy camper" <keenan@p2sol.com> wrote in message
news:1194475781.628923.259830@k79g2000hse.googlegr oups.com...
> On Nov 7, 4:04 pm, "Gilgamesh" <gilgamesh4e...@aol.com> wrote:
>> We've got a database which contains encrypted data. To encrypt and
>> decrypt
>> the data we use 3DES algorithm which is encapsulated into a C# assembly.
>> How
>> do we incorporate this algorithm into SSAS? We SSAS as the backend for
>> the
>> reporting services so it's important that reporting services sees the
>> data
>> in decrypted.
>>
>> Thanks,
>> Gilgamesh
>
> I found this in the SSAS documentation:
>
> To this end, Analysis Services provides the ability to add assemblies
> to an Analysis Services instance or database. Assemblies allow you to
> create external, user-defined functions using any Common Language
> Runtime (CLR) language, such as Microsoft Visual Basic .NET or
> Microsoft Visual C# .NET. You can also use Component Object Model
> (COM) automation languages such as Microsoft Visual Basic or Microsoft
> Visual C++.
>
> Assemblies let you extend the business functionality of MDX and DMX.
> You build the functionality you want into a library, such as a dynamic
> link library (DLL) and add the library as an assembly to an instance
> of Analysis Services or to an Analysis Services database. The public
> methods in the library are then exposed as user-defined functions to
> MDX and DMX expressions, procedures, calculations, actions, and client
> applications.
>
>
> However, I doubt that answers your question. Can you elaborate a bit
> on the issue or provide an example? I can certainly understand
> encrypting sensitive data in an OLTP database (e.g. financial or
> medical information) but a core aspect of a data warehouse is the
> ability to aggregate measures. If the measures are encrypted I don't
> know if OLAP will provide any performance incentive.
>
>