Creating Employee Dimension and Importing data

  • dean gross

    SSCrazy

    Points: 2632

    I have the following table in another SQL DB. I am working on a plan for a new SSAS cube and think that I need an Employee dimension, Geography dim and Company Organization dim that will be based on this table. After I create these dimensions, what is the recommended best practice for extracting the data from this table. Also, am I on the right track or is there a better approach for designing the dimensions.

    TIA

    Dean

    SELECT [Employee_Number]

    ,[Last_Name]

    ,[First_Name]

    ,[Middle_Initial]

    ,[Nick_Name]

    ,[Office_Phone]

    ,[Mobile_Phone]

    ,[Fax]

    ,[Pager]

    ,[Email]

    ,[Alternate_Phone]

    ,[Alternate_Fax]

    ,[Contact_Comments]

    ,[Functional_Title]

    ,[Department_Name]

    ,[Project]

    ,[Business_Unit]

    ,[Manager_Employee_Number]

    ,[Manager]

    ,[Employment_Status_Code]

    ,[Employee_Status]

    ,[Corporation]

    ,[Company_Number]

    ,[Company]

    ,[Division_Code]

    ,[Division]

    ,[Sector_Code]

    ,[Sector]

    ,[Department_Code]

    ,[Department]

    ,[Discipline_Code]

    ,[Discipline]

    ,[Work_Location_Code]

    ,[Office_Description]

    ,[Room_Number]

    ,[Office_Address_1]

    ,[Office_Address_2]

    ,[Office_City]

    ,[Office_State]

    ,[Office_Postal_Code]

    ,[Office_Country]

    ,[Alternate_Description]

    ,[Alternate_Address_1]

    ,[Alternate_Address_2]

    ,[Alternate_City]

    ,[Alternate_State]

    ,[Alternate_Postal_Code]

    ,[Alternate_Country]

    ,[Network_Id]

    ,[Alternate_Job_Title]

    ,[Date_Last_Updated]

    FROM [PARFLS].[dbo].[HrMyData]

  • Hendra Eka Putra

    SSCrazy

    Points: 2015

    You have a table in other database server, lets call in DB1 in ServerA. Then you created a SSAS Solution using BIDS, use DB1 as a data source, created a dimension and cube, and deployed the SSAS solution, for example to ServerB.

    You want to make sure that your OLAP cube will have the same data with DB1.

    You can create a SSIS package using BIDS, and then add Process Cube task to the package. Set the required parameters, and then deploy the package to your SSIS server. Create a job schedule to run the package, for example every 12 hour.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply