Cubes of some customers in Analysis Services

Asked

Viewed 187 times

3

I intend to allow my customers to access their data to generate customized reports and use the power of Powerpivot Excel. Today all customers (business) are in the same database and for the little I know about cubes I designed the following strategy.inserir a descrição da imagem aqui

The cubes can stay on other servers (Azure, Amazon, dedicated physical server,...). The important thing is that they will be generated from the one main database (with all the data).

My questions:

  1. I can work this way?
  2. How do I create a hub by restricting id_bus? I am using SQL Server Business Intelligence Development Studio 2008.
  3. I can create a user who has read-only access to a specific cube?
sql-server business-intelligence

1 answer

3

I was able to solve my demand using SSIS (Integration Services) with Visual Studio.

  • I create the Azure SQL (Basic) bases with the tables I want to publish to my client
  • In SSIS I created a package (package) for each client with the data source and destination connection. I also created a variable which is the customer identifier in which filters will consider.
  • I published the project in the Integration Services Catalog database and programmed a JOB to run the Packages all morning.
  • For my client I provide the access credentials to the Azure database (read-only user) in which he will use in Excel Powerpivot.

Follow the link of a good SSIS tutorial. It is worth giving an exploration for those who demand similar or have some periodic work in data extraction/migration/transformation. You can even generate Excel spreadsheets with the data.

SSIS Tutorial with Azure SQL

Browser other questions tagged sql-server business-intelligence

You are not signed in. Login or sign up in order to post.