The Acumen software design blog provides you with helpful articles about Databases.

This web site provides a good overview.

Database Wikipedia

HOWTO: Backup/Restore MongoDB w/ PHP

, ,

Dumping a MongoDB database is done using the “mongodump” utility in the command prompt. This dump creates a binary export of the database. This export can be restored using the “mongorestore” utility.

A basic dump of a local MongoDB database can be code as follows:

mongodump --db mymongodatabase

To dump a specific collection, simply specify the collection:

mongodump --collection collection --db mymongodatabase

Also, you can dump the database to a specific server location:

mongodump --db mymongodatabase --dbpath /var/mypath/mongodumps

We wanted to create a utility that would allow our client to backup their WordPress-integrated MongoDB database using an admin utility. To do this, we built a PHP class that allows us to enter the database name, the dump location, and go. This tool also ZIPs the binary files for us, allowing us to conserve disk space and contain each dump in one file. You can access this class here: https://github.com/KerryRitter/MongoDumper

A basic usage of this class is done as follows:

$dumper = new MongoDumper("/var/mypath/mongodumps");
$dumper->run("mydb", true); // 'true' shows debug info

This will dump the local ‘mydb’ database to /var/mypath/mongodumps/mydb_[timestamp].zip and display the shell output and various debug info. To remove the debug info, remove the “true” parameter in the “run” function call.

To restore, unzip the BSON and JSON files and upload to a folder on the server with the name of the database you wish to restore to. For example, if you wish to restore “mydatabase”, you will need to put the files in a path such as /var/mypath/mongodumps/mydatabase. To restore the database, use the following command in your command prompt:

mongorestore /var/mypath/mongodumps/mydatabase

There are a number of options available for restoration that you can find in the documentation: http://docs.mongodb.org/manual/tutorial/backup-with-mongodump/

Please note that the published MongoDumper class is the simple core; there are security concerns with using shell_exec that need to be addressed before you use it on a publicly accessible site. If there is an error given by the dump or the dump did not work, make sure to check the MongoDumper.php file and the backup folder’s permissions.

/3 Comments/by

SSAS – Attribute Hierarchies Error

, ,

SQL

In his excellent book, Star Schema, Christopher Adamson begins Chapter 7, “Hierarchies and Snowflakes,” with the following language:

It is possible to describe a dimension table as a series of parent-child relationships among groups of attributes. Days make up months, months fall into quarters, and quarters fall into years, for example. This chapter explores these attribute hierarchies and their implications for the design and use of dimensional databases.

Page 147. This understanding appears fully consistent with the definition of found in MSDN:

Attribute hierarchy

An attribute hierarchy is a hierarchy of attribute members that contains the following levels:

  • A leaf level that contains each distinct attribute member, with each member of the leaf level also known as a leaf member.
  • Intermediate levels if the attribute hierarchy is a parent-child hierarchy.
  • An optional (All) level (IsAggregatable=True) containing the aggregated value of the attribute hierarchy’s leaf members, with the member of the (All) level also known as the (All) member.

http://msdn.microsoft.com/en-us/library/ms144884.aspx
It may seem intuitively obvious what the phrase “Attribute Hierarchy” means, and the descriptions given by Mr. Adamson and the definition given in MSDN probably fit neatly with your intuition. If you follow your intuition, however, other documentation for Microsoft SQL Server Analysis Services (SSAS) becomes confusing, and you are unlikely to leverage features of the product that will make it more useful and responsive.

Microsoft Documentation

To cut to the chase, what you need to know is this: In most of Microsoft’s documentation (other than the definition shows above) and within the user interface of Business Intelligence Development Studio (“BIDS”), “attribute hierarchy” means a hierarchy with exactly one attribute. Intuitively, a hierarchy with only one attribute may not seem like much of a hierarchy at all to most people. By default, however, it does contain two levels: The All level and the level under the All level with (by default) the same name as the attribute.

MSDN defines an additional type of hierarchy, the “user-defined hierarchy.” It is defined in the following way (Attribute Hierarchies, from MSDN attributes and attribute hierarchies) :

User-defined hierarchy

A user-defined hierarchy is a balanced hierarchy of attribute hierarchies that is used to facilitate browsing of cube data by users. User-defined hierarchies do not add to cube space. Levels in a user-defined hierarchy can be hidden under certain circumstances and appear unbalanced.

Clearly, this definition contemplates the possibility of more than two levels created from more than one attribute, and this definition of “user-defined hierarchy” seems to be the same as our intuitive understanding of “attribute hierarchy.” But they are in fact different within the Microsoft world.

So… Why should we care?

Why do we care? Because in SSAS, individual attributes contained in dimensions have properties that only make sense if a stricter definition of attribute hierarchy is used–a definition that refers to hierarchy with exactly one attribute level and (usually) an All level. Look at the properties window for a single attribute contained in a dimension:

SSAS Screen Shot

Notice that there are several properties of the attribute that have “Attribute Hierarchy” contained within them and that these properties are not properties of a user-defined hierarchy. The description contained in gray-highlighted box at the bottom of the properties window only makes sense if “attribute hierarchy” and “user-defined hierarchy” mean different things.

Developers define user-defined hierarchies in the “Hierarchies” panel of the Dimension Structure tab.

SSAS Screenshot SQL

In conclusion, be aware that when you are reading Microsoft documentation and using the SSAS interface, “attribute hierarchy” and “user-defined hierarchy” are two very distinct things, and failing to understand this will result in confusing properties that only apply to attribute hierarchies to user-defined hierarchies.

/0 Comments/by

HOWTO: Improve SQL Server in Third-Party App

, ,

SQL Server 

Description:

We have encountered the unfortunate situation where a company has a third-party application and the performance problems associated with this application cannot be solved despite many in-house efforts to do so and many attempts to get cooperation from the vendor in troubleshooting the root cause of the problem.

The vendor has often come back with demands for additional or more powerful hardware or greater network bandwidth where none of the suggestions fix the problem. The company using the software doesn’t know what to do next, especially when the software product is well-known in the industry or is from a major software vendor. Customers don’t know where to go next to seek solutions when the vendor will not cooperate.

When a problem is addressed without cooperation from the third-party vendor, the vendor will usually no longer support the application. This is not an acceptable solution even if solutions are found. The vendor must be persuaded to help in order to effect a real solution. The customer is held back by its own lack of confidence in dealing with the problem to demand a solution.

Many third-party vendors hide behind flimsy policies regarding customer information to refuse to share information they have gathered from other customers which would clearly show that the problem is with their software. They use excuses and tactics which can often be easily disproven without their cooperation. For example, third party vendors often assert that a customer is using their product in a way that is different than other customers; even if there is a problem, the vendor cannot fix the problem without adversely affecting other customers. I have often found that this is nonsense.

SQL Server Problem:

The problem starts when the vendor allocates resources for development of their product. The vendor has or acquires a great deal of knowledge of the business process necessary for the product and designs a great user interface to implement the required functionality.

Because there isn’t a lot to “see” from good data architecture work during the development phase, the vendor has never seriously considered the database as an important part of the application requiring expert resources for design or optimization.

This is true even with Microsoft, where the SharePoint product clearly has many continuing design and tuning issues. (Microsoft continually gets feedback from within the company on this product and in a mind-blowing use of compartmentalization and denial continues to refuse to address these issues.)

Some companies can be persuaded or embarrassed into taking these issues seriously when presented with undeniable evidence and customers who refuse to accept nonsense for excuses.

There are many things that can be done even after deployment. These remedial actions can be implemented as a result of experienced analysis and interpretation of performance monitor traces, results from dynamic management views and SQL Server Profiler traces. Acumen has the experience to do this, and to teach you to do this as well.

As a result of this an engagement where we analyze the use of SQL Server in a third party application, Acumen can provide the following, where appropriate.

Steps:

  1. A written report targeted to a particular application that proves that problems with the application are harmfully affecting performance, if such is the case.
  2. Knowledge transfer from Acumen to you so that you have confidence to pressure the vendor to provide a fix and evidence that the required fixes will not negatively affect performance.
  3. An Acumen consultant can participate in conference calls (or in-person visits) where Acumen becomes your advocate for receiving fixes to your legitimate issues.
  4. Occasionally, Acumen may have previously experiences with this software to defeat claims or implications from the vendor that the problem is yours alone.
/0 Comments/by

SOLVED: Access Dialog Issue

, ,

Microsoft Access kept hanging on when .Show was called the second time in an application. I created a simple example showing it either hanging or killing the Access 2010 program running windows 7.

Clicking the first button and then the second button kills the Access program (“Access has stopped working…”). Clicking the first button and then the third button hangs, and you have to use the Task Manager to kill the application.

Changing the argument in btnBrowse1_Click to msoFileDialogPicker makes it all work. Various combinations of declaring f as FileDialog throughout produces various results, not all of them successful.

Option Compare Database – Access 2010

Private Sub btnBrowse1_Click()
Dim f As Object
Dim varfile As Variant

Set f = Application.FileDialog(msoFileDialogOpen) ' Argument was supposed to be msoFileDialogPicker
    f.initialfilename = "*.xls"
If f.Show Then
    If f.SelectedItems.Count > 0 Then
        For Each varfile In f.SelectedItems
            txtFilename = varfile
        Next
    End If
End If

End Sub

Private Sub btnBrowse2_Click()

Dim f As FileDialog
Dim varfile As Variant

Set f = Application.FileDialog(msoFileDialogFilePicker)
    f.initialfilename = "*.xls"
If f.Show Then
    If f.SelectedItems.Count > 0 Then
        For Each varfile In f.SelectedItems
            txtFilename = varfile
        Next
    End If
End If

End Sub

Private Sub btnBrowse3_Click()

Dim f As Object
Dim varfile As Variant

Set f = Application.FileDialog(msoFileDialogFilePicker)
    f.initialfilename = "*.xls"
If f.Show Then
    If f.SelectedItems.Count > 0 Then
        For Each varfile In f.SelectedItems
            txtFilename = varfile
        Next
    End If
End If

End Sub

FileDialogTest Access 2010

For information about how Acumen can help with your Software needs, contact us today!

/0 Comments/by

Move Crystal Report between Servers

,

Crystal Report

Crystal Report

Object reference not set to an instance of an object.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

CrystalReportSource1.ReportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, False, “Registration Reports”)

This occurs because the Login credentials provided are not valid.  In particular, you cannot change SERVER or DATABASE with

SetDatabaseLogon(User, Password, Server, Database)

See:   http://msdn.microsoft.com/en-us/library/ms227750(v=vs.80).aspx

Code:

              First:        Dim Server As String = ConfigurationManager.AppSettings(“DBServer”)

              Second:    Dim Database As String = ConfigurationManager.AppSettings(“Database”)

              Third:       Dim User As String = ConfigurationManager.AppSettings(“DBUser”)

              Last:         Dim Password As String = ConfigurationManager.AppSettings(“DBPassword”)

 

                ‘ The following set of code is necessary to dynamically change the SERVER and DATABASE.

                Dim ci As CrystalDecisions.Shared.ConnectionInfo = New ConnectionInfo()

                ci.Type = ConnectionInfoType.CRQE

                ci.ServerName = Server

                ci.DatabaseName = Database

                ci.UserID = User

                ci.Password = Password

                Dim tli As TableLogOnInfo = New TableLogOnInfo()

                tli.ConnectionInfo = ci

                Dim t As CrystalDecisions.CrystalReports.Engine.Table

                For Each t In CrystalReportSource1.ReportDocument.Database.Tables

                    t.ApplyLogOnInfo(tli)

                Next t

                CrystalReportSource1.ReportDocument.SetParameterValue(“@var1”, Request.QueryString(“param1”))

                ‘ The following will fail if the login fails:

                ‘        “Object reference not set to an instance of an object”

                ‘        “Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.”

                CrystalReportSource1.ReportDocument.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, False, “Registration Reports”)

 

For more information how Acumen can help with your software needs, contact us today!

/0 Comments/by

Aptify Views and SQL Temporary Tables

,

Building additional SQL views in Aptify Association and Membership Management Software can sometimes require you to use SQL temporary tables in your view.

If you then attempt to create a database object from said view:

You will get an Aptify Exception error that will include “Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.”

To get around this problem:

Take the T-SQL involving the temp table and port it into a table-based function that is called by the view.

The issue comes from the way Aptify enumerates everything returned by the SELECT statement and cannot handle temp tables in a view. Functions don’t have the same restrictions, though, and can be used to return the needed data.

See our Network Page or Contact Us to find more ways we can help you!

/0 Comments/by