Each Model defined has a FACT table with the name FACT_ModelName_default_partition.
This table contains the following fields:
- Dimensionname_MemberId - there is one field for each dimension assigned to the Model. The field contains bigint values defining the member on the dimension that the piece of data in the record is for. Null values are not allowed in these fields. The bigint value in this field must match a MemberId value in the dimension table named DS_DimensionName.
- ModelName_Value - a floating point value. This is the number processed into the Analysis Services cube for reporting. These values are not stored with the same signs used for reporting. The signs of the data storage are the same as typical General Ledgers. If the incoming data is in reporting signs, multiple the value by the value in the Sign field of the Account type dimension before writing to this table. Null values are allowed in this field. Normally there will only be one record for any unique combination of dimension MemberIds, however that is not required. If there are multiple records for a unique combination of dimension MemberIds, the values from the multiple records are added together for one value in the Analysis Services cube.
- ChangeDatetime - a datetime field that should be populated with the date and time the record was last changed. This can be done using the GetDate() SQL function. While null values are allowed in this field, a null value is NOT recommended.
- Userid - an nvarchar(100) field to contain the windows userid of the user that made the change to the record. While null values are allowed in this field, a null value is NOT recommended.
Note: There is also a view created for the FACT table to make it easier for administrators to view their data in SQL. The name is FACT_ModelName_View. This view displays values with member labels instead of the numeric MemberId.
To load data with SSIS, you need to update the appropriate records in the FACT table for the model. In your SSIS package you could choose to delete the appropriate subset of records first, and then insert new records, or you could choose the modify the existing records and insert any new records. Either approach can produce the correct results and is acceptable. After updating records in the FACT table, process the data into the Analysis Services cube with the standard Analysis Services Processing task.