Four Classes Of Databases Systems


Hello everybody,

I want to leave a short note on four types of database systems:

  • OLTP
  • DSA
  • DW
  • ETL

Online transactional processing

This type of systems is used for data entry, basically for CRUD operations. As usually some rules of normalization is applied there. Also OLTP is not suitable for reporting purposes because processing normalized data takes a lot of computing powers. That's why next type of systems is used:

Data warehouses

This kind of systems is used for data-retrieval and reporting purposes. Mainly it's structure is optimized for data-retrieval needs. Quite often tables have redundancy, fewer tables, simpler relationship between tables. You can learn a bit more about data warehouses via googling of data warehouse + star schema, data warehouse + facts table,  data warehouse + data mart, etc. 


The process of pulling data from OLTP is named extract, transform, load. For such purposes often used Microsoft SQL Server Integration Services ( SSIS ) for handling ETL needs.


Quite often ETL is implemented not as one step, but in stages, of creating separate stages and locating those stages on separate databases. Then those databases belong to Data Staging Area. 


Operator In In Bql


Hello everybody,

today I want to write a few words about operator in which was presented in SQL for long ago, but weren't available in Acumatica BQL. But time goes on and now you can use it. For example like this:

Object[] values = new String[] { "BXW000004""BXW000005" };
                POOrder item = PXSelect<POOrder,
                    Where<POOrder.orderNbrIn<Required<POOrder.orderNbr>>>>.Select(Base, values);

that code will generate following sql statement:

Select * from POOrder POOrder Where POOrder.OrderNbr In ('BXW000005', 'BXW000004')
	Order by POOrder.OrderNbr

I can say that such approach simplifies some tasks that require dynamic passing of arguments.

No Comments

Add a Comment


How To Add Sitemap Into Your Customization


Hello everybody,

today I want to describe some simple steps of how to add into your customizaiton sitemap. As you already know, sitemap of Acumatica is saved in database. And if you make customization of your sitemap you may need to add to your project.xml something like this:

<Customization level="0" description="" product-version="5.30">
            <relations version="3" main-table="SiteMap" />
                <table name="SiteMap" />
					<row Position="72" Title="Warehouse" 
Url="~/Frames/Default.aspx" Expanded="0" IsFolder="0" 
NodeID="95A4548D-CA01-4485-992F-FADE1597DC95" ParentID="00000000-0000-0000-0000-000000000000" /> <row Position="1" Title="Producing" Url="" Expanded="0" IsFolder="0" ScreenID="" 
NodeID="42C9E271-EC9D-483E-9A8F-2912E3E7C2ED" ParentID="95A4548D-CA01-4485-992F-FADE1597DC95" /> <row Position="2" Title="Food Administratio" Url="" Expanded="0" IsFolder="0" ScreenID="" NodeID="D9CA98E4-73CC-4DEB-BFED-35EFFD72274C" 
ParentID="95A4548D-CA01-4485-992F-FADE1597DC95" />         <!-- Pages  --> <row Position="1" Title="Management of materials" Url="~/Pages/TC/TC100000.aspx" Expanded="0" IsFolder="0" ScreenID="TC100000" 
NodeID="4083D0D7-3FC1-412F-A3FA-E7D730F3EBFA" ParentID="42C9E271-EC9D-483E-9A8F-2912E3E7C2ED" />                 </SiteMap>             </data>         </data-set>     </SiteMapNode>

For now I'd like to talk only about sitemap node. After I created sitemap node once, soon I got request to modify it and for me it was a bit painful from standpoint of regenerating of xml nodes.

So I decided to write some simple sql statement that allows me to to create sitemap manually in Acumatica, and then read it from db in convenient way for me. 

Below goes that SQL:

select '<row Position="'+ CAST(s.Position as nvarchar(MAX)) +'" Title="' + s.Title  
	+ '"'
	 + ' Url="'  + COALESCE(s.Url, '' ) + '"'
	+ ' Expanded="' + CAST(s.Expanded as nvarchar(1)) + '"'
	+ ' IsFolder="' + CAST(s.IsFolder as nvarchar(1)) + '"'
	+ ' ScreenID="' + COALESCE(s.ScreenID, '') + '"' 
	+ ' NodeID="' + CAST(s.NodeID as nvarchar(MAX)) + '"'
	+ ' ParentID="' + CAST(s.ParentID as nvarchar(MAX)) + '"'
	+ ' Description="' + COALESCE(s.Description, '') + '"' + ' />' 
	as SiteMapRow
	from SiteMap s where CreatedDateTime > '2017-09-12 12:00:00' order by Position

with that SQL you can simply copy/paste generated result into your customization

No Comments

Add a Comment


How To Make Yourself As Administrator In Acumatica With Sql


Hello everybody,

today I want to share some piece of SQL code that can help you to add yourself to Administrators group in Acumatica:

INSERT INTO [dbo].[UsersInRoles]
         ,'2010-07-27 17:25:00.000'
         ,'2010-07-27 17:25:00.000')

With this sql you can make yourself as administrator in Acumatica instance.

Also if you wish you can reset password of admin with following sql:

UPDATE Users SET Password = 'abc', LockedOutDate =null WHERE Username = 'admin'

and log in into Acumatica. There you can add to your account additional permissions

No Comments


Add a Comment