none
How can I recreate the Time dimension? RRS feed

  • Question

  • I created a Time dimension which created a Time Table in the database. Afterwards I changed the database in the Data Source. Now I am trying to reprocess the Time dimension and I get the following error:

    When I try to browse the data, I also get an error:

    Obviously, the Time table does not exist in the new database. How can I recreate it?

    Thursday, February 1, 2018 8:44 AM

All replies

  • Error looks like the table dbo.Time doesnt exist in the database to which your data source points

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, February 1, 2018 8:51 AM
  • Hi Pappasa,

    Thanks for your question.

    >>>Obviously, the Time table does not exist in the new database. How can I recreate it?
    In this scenario, please refer to below blog to create time dimension from scratch:
    Creating Date dimension from scratch in Microsoft SSAS


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Thursday, February 1, 2018 11:39 AM
    Thursday, February 1, 2018 9:07 AM
    Moderator
  • Is it not possible to just recreate the Time table on the database? If I create a new dimension, I will have to create again all the relationships. This is tedious, if I have to change the database often.
    Monday, February 5, 2018 3:21 PM
  • Hi Pappasa,

    Thanks for your response.

    >>>Is it not possible to just recreate the Time table on the database? If I create a new dimension, I will have to create again all the relationships. This is tedious, if I have to change the database often.

    It seems that the time table does not exist in your new database, after you have changed the database in the data source as you stated. In this scenario,You can generate a script that will build whichever tables you wish from the old database of your SSAS project as well as insert the data in those tables
    To do this follow these steps in SQL Server Management Studio:

    1.Right-click on your database and select Tasks > Generate Scripts

    2.In the Generate and Publish Scripts wizard, select the "Select specific database objects" option

    3.Expand the "Tables" tree and select the time table you wish to export the scheme and data for, then click Next

    4.In the next screen choose how you wish to save the script (the Output Type must remain set as "Save scripts to a specific location"), then click the Advanced button in the top right corner

    5.In the newly opened window, under the General section is a setting called "Types of data to script", set this to "Scheme and data" and click OK

    6.Click Next, review the export summary and click Next again. This will generate the script to your selected destination.

    7.Run this script in the database you have changed to.

    You can also refer to below document:
    https://technet.microsoft.com/en-us/library/ms178078(v=sql.105).aspx


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 6, 2018 7:13 AM
    Moderator
  • Is it not possible to just recreate the Time table on the database? If I create a new dimension, I will have to create again all the relationships. This is tedious, if I have to change the database often.

    Question is how it got removed from your model in the first place?

    Yes as you say its tedious getting it back again as you're to redfine all relationships

    Do you have an old backup of cube handy by any chance?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, February 6, 2018 7:18 AM