Home > FEP 2010 > Recreating FEP 2010 Olap database

Recreating FEP 2010 Olap database

So recently I ran into an issue where a customer was experiencing issues with the FEP 2010 Olap database. No data was being shown in the FEP SQL Analysis database, and errors were showing in the Application event log as follow:

MSSQLServerOLAPService
Errors in the metadata manager. An error occurred when loading the Collection dimension, from the file, ‘\\?\D:\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_ABC.0.db\Collection.4141.dim.xml’.

MSSQLServerOLAPService
Errors in the metadata manager. An error occurred when loading the FEP cube, from the file, ‘\\?\D:\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_ABC.0.db\FEP.33097.cub.xml’.

SQLISPackage100
Package “ssisFEP_OlapProcessing” failed.

Doing a little research it looked like the old Olap database needed to be dropped and the creation of a new FEP Analysis database need to be done. There are a couple of executables in the folder where FEP has been installed to. In this example we will say that FEP lives in the “D:\Program Files\Microsoft Forefront\Forefront Endpoint Protection” folder. There are 2 executables in the folder called EnterpriseSecurityDW.exe used for SQL 2005 and EnterpriseSecurityDW2008.exe used for SQL 2008 and 2008 R2.

For this example we will use the following names for SQL server name, SQL database name, Olap database name, FEP reporting user:

SQL Servername – SQL
SQL Database Name – FEPDW_ABC
OlapDb: FEPDW_ABC
FEP reporting user: domain\fepreporting

On the SCCM/FEP server, open up an Administrator command prompt and browse to the “D:\Program Files\Microsoft Forefront\Forefront Endpoint Protection” folder.

Type the following command for a SQL 2008 or 2008 R2 server:

EnterpriseSecurityDW2008.exe /serverName:SQL /sqlDatabaseName:FEPDW_ABC /OlapDb:FEPDW_ABC /reportsUser:domain\fepreporting /overwriteDatabase:1

Type the following command for a SQL 2005 server:

EnterpriseSecurityDW.exe /serverName:SQL /sqlDatabaseName:FEPDW_ABC /OlapDb:FEPDW_ABC /reportsUser:domain\fepreporting /overwriteDatabase:1

* Note that the arguments are case sensitive

The command should execute and give you a successfully completed at the end.

Advertisements
Categories: FEP 2010
  1. Joakim
    March 15, 2012 at 8:27 pm

    Hi Jason,
    I tried to run the command line you blogged about (EnterpriseSecurityDW2008.exe /serverName:SERVERNAME /sqlDatabaseName:FEPDW_XXX /OlapDb:FEPDW_XXX)
    But I recived a error message, do you know how I could solve this issue?

    Thanks and best regards
    Joakim

    EnterpriseSecurityDW2008 started…Version:2.0.0657.0

    OLAPCubeGenerator : Error while generating/deploying OLAP Cube
    Error : Microsoft.AnalysisServices.OperationException: File system error: The following file is corrupted: Physical file: \\?\e:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_XXX.0.db\Time.0.dim\37285.120HoursIdUTC.kstore. Logical file .
    Errors in the metadata manager. An error occurred when loading the Time dimension, from the file, ‘\\?\e:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_XXX.0.db\Time.37285.dim.xml’.
    Errors in the metadata manager. An error occurred when loading the FEP cube, from the file, ‘\\?\e:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_XXX.0.db\FEP.298093.cub.xml’.

    at Microsoft.AnalysisServices.XmlaClient.CheckForException(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
    at Microsoft.AnalysisServices.AnalysisServicesClient.Discover(IMajorObject obj, ObjectExpansion expansion)
    at Microsoft.AnalysisServices.Server.Refresh(IMajorObject obj, ObjectExpansion expansion)
    at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
    at Microsoft.Forefront.EndpointProtection.EnterpriseSecurityDW.ConnectToOLAPServer()
    at Microsoft.Forefront.EndpointProtection.EnterpriseSecurityDW.Main(String[\[]] args)
    Error : at Microsoft.AnalysisServices.XmlaClient.CheckForException(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
    at Microsoft.AnalysisServices.AnalysisServicesClient.Discover(IMajorObject obj, ObjectExpansion expansion)
    at Microsoft.AnalysisServices.Server.Refresh(IMajorObject obj, ObjectExpansion expansion)
    at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
    at Microsoft.Forefront.EndpointProtection.EnterpriseSecurityDW.ConnectToOLAPServer()
    at Microsoft.Forefront.EndpointProtection.EnterpriseSecurityDW.Main(String[\[]] args)

    • March 15, 2012 at 8:33 pm

      What version of SQL are you running currently?

  2. Joakim
    March 28, 2012 at 9:04 am

    Hi
    Im running SQL Server 2008 R2 (10.50.1790)

  3. Mike
    June 8, 2012 at 9:09 pm

    This post sent me on the right direction. One thing I wanted to add was we had the same problem listed above. What we found was that if you remove the files that it errors on, then run the command, it will give an error of the file missing. If you put the file back it will complain about a new file. If you do this through the files, the job will run. We had no cube settings and by removing the problem file, running the command, putting the file back, and running the command it would repair each of the files and eventually kicked off rebuilding the cube settings.

    Dont ask how we figured this strange fix. Use at your own risk. This will resolve the exact issue Joakim posted

    • June 11, 2012 at 1:31 pm

      Thanks for the post Mike, this definitely was not a well documented issue so I can imagine people will have different results. This was a very interesting fix while I was working with one of my customers. Glad this was able to point you in the right direction.

  4. August 28, 2012 at 8:28 am

    I had to use a similar syntax:
    enterprisesecuritydw2008 /serverName:SQL /sqlDatabaseName:FEPDW_ABC /olapDatabaseName:FEPDW_ABC /reportsUser:domain\fepreporting /overwriteDatabase:1

    As with Joakim, I was getting a similar error rebuilding.
    I had to delete the contents of the C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\FEPDW_ABC.0.db folder
    as per
    http://social.technet.microsoft.com/Forums/en-US/FCSNext/thread/58a3468c-ca2c-4139-827c-3ee3191bfe57/

  5. jonathanchristie
    August 28, 2012 at 8:29 am

    I had to use a similar syntax:
    enterprisesecuritydw2008 /serverName:SQL /sqlDatabaseName:FEPDW_ABC /olapDatabaseName:FEPDW_ABC /reportsUser:domain\\fepreporting /overwriteDatabase:1

    As with Joakim, I was getting a similar error rebuilding.
    I had to delete the contents of the C:\\Program Files\\Microsoft SQL Server\\MSAS10_50.MSSQLSERVER\\OLAP\\Data\\FEPDW_ABC.0.db folder
    as per
    http://social.technet.microsoft.com/Forums/en-US/FCSNext/thread/58a3468c-ca2c-4139-827c-3ee3191bfe57/

    • Adam
      October 5, 2012 at 1:54 pm

      jonathanchristie’s steps worked for me:
      1. Stopped Analysis services
      2. Deleted the contents of the C:\\Program Files\\Microsoft SQL Server\\MSAS10_50.MSSQLSERVER\\OLAP\\Data\\FEPDW_ABC.0.db folder
      3. Restarted analysis services
      4. Redeployed the reports with his syntax
      5. Re-processed each cube and measure group

      • Shaun
        March 7, 2014 at 10:11 am

        Hi Adam,

        I have worked through the steps and the command jonathanchristie gives runs and completes fine however im still getting the error could you explain your step 5 on how to reprocess each cube and measure group. Thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: