1

Every year we keep a historical copy of one of our cubes. This year someone decided they wanted to pay us money to add an attribute to the cube which did not previously exists. Fine, I like money, but the issue is we don't have a backup of the database that we built this cube off of.

So a question arises in my head, do we need that original database to add a new attribute to this cube? Is it possible for us to add a new attribute to the cube and only process this attribute without having the cube orignal datasource?

Not having a great understanding of what is happening under the hood when I add an attribute to a SSAS cube and process, I can't say if this is or isn't possible. I could imagine that possibly, the cube has a snapshot in memory of the datasource that it can work off of. I can also imagine that this would be ridiculously inefficient so there is a chance this is no way in heck possible

EDIT: It at least would seem feasible to add a calculated member that makes use of existing data in the cube.

I also should mention that I tried to add an attribute to such a cube and received an error:

"Dimension [Partner] cannot be saved File system error failed to copy file C:\\MYSQLSERVER\OLAP\DATA\2013_Cube.db\\.dim\.dstore to C:\\MYSQLSERVER\OLAP\DATA\2013_Cube.db\\.dim\.dstore file exists"

Sorry I faked those filepaths a little.

stevebot
  • 23,275
  • 29
  • 119
  • 181

2 Answers2

2

This task is very difficult. The only way I can imagine would be to manually reconstruct the original database based on the Data Source View (it has cached metadata), and then try to generate the data to populate it using a SSAS query tool (e.g. Excel, SSRS, OLE DB Provider for Analysis Services).

If you want to add one attribute in a dimension, you might be able to limit that effort to the source data for the dimension in question.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
1

First let me explain based on the steps of the process how a cube stores the data!!!

  1. Get the datasource - data!!! That is get access to the original databases/files etc. At this point all the data are at the primary source. All data are normalized one way or the other.
  2. Construct a data warehouse. ELT process. At this point you combine all your data in a denormalized wharehouse, without foreign keys or any constraint. All data are now in an intermediate state in a denormalized sql database and ready to be used in the cube.
  3. Construct the OLAP cube. The Data Warehouse is now your data-source. All data are now aggregated in rows inside the cube with their corresponding values. The redundancy is enormous and the data are 100% denormalized, they hardly follow a patern (Of course they do but it is not always easily understandable).

An example at this state would be a row like this

Company -> Department -> Room | Value(Employees)
ET LTD  -> IT -> Room 4 -> | 4

The exactly same row would exist for Value(Revenue). So in essence all data exist inside the SSAS Database (The cube). Reconstructing the Database would mean a Great Deal of reverse engineering.

You could make a new C# program using MDX connectors and queries to get the data, and MSsql connectors to save them inside an OLTP database. MDX has a steep learning curve and few citations on websites, so the above method is not advisable.

There is no way that I know of to get the data from excel, as excel gets the pivot table data in a dynamic way from the DataConnection.

Athanasios Kataras
  • 25,191
  • 4
  • 32
  • 61