Artículos IT, información y publicaciones sobre SQL

Últimas publicaciones destacadas en Dataprix sobre esta temática TIC

How to use Openquery to make a join between a SSAS cube and a table from any other database

 

Connetion to SSAS instanceImagine you need a report with data from an OLAP sales cube, and have to include data from a table of the relational data source, or from an external database.

With a linked server you can construct a MDX query in the SQLServer instance where you have the cube, joining with an external database to complete the data with information from tables of the relational database..

Avoiding errors concatenating fields with numerical values in SQL Server

 

concatenate stringsThe operator to concatenate in SQL Server is '+', but this operator is also used to add values.

 

Being the same operator for both, the query analyzer makes a concatenation or a sum based on the type of data fields being treated. If the fields are of 'string' type, the operator concatenate, and if it has numeric fields, two integers, for example, it add the two values.

UPDATE with JOIN in ORACLE

 

Suppose we want to update in our ORACLE database the costs fields of the fact table FAC_TABLE with the unit cost of our table COSTS.

We can do this in two ways:

1. (Slow, but valid for a few data or to sporadic uses)

update FAC_TABLE ft  set UNIT_COST = (select distinct UNIT_COST from COSTS ct  where (ft.id_article = ct.id_article);

2. (The best way is this, and the performance is ideal if you have constraints)

UPDATE ( SELECT ft.UNIT_COST AS old_cost,  ct.UNIT_COST AS new_cost FROM FAC_TABLE ft  INNER JOIN COSTS ct ON ft.id_article ct = ct.id_article) )  SET old_cost = new_cost;

To the proper functionality of this second option you need a UNIQUE or PRIMARY KEY constraint on ct.id_articulo. 
If you don't have this constraint, you can use the hint / * + BYPASS_UJVC * / after the word UPDATE (Bypass update join view constraint).

The performance increase if we have the constraint but even without it, the second option should run quite faster than the first option.

 

Remote access using Oracle DBLINK

 

The easiest way to access from an Oracle database objects from another Oracle database is using a DBLINK (being the easiest does not mean that it is always the most desirable, the abuse of DBLINKS can create many problems, both of performance and safety)

To do this it's necessary a user with CREATE DATABASE LINK privilege, and create a DBLINK in the source database (A) by a simple statement such as:


Create database link LNK_from_A_to_B connect to USER identified by PASSWORD USING 'B'; 

'LNK_from_A_to_B' is the name of the link, 'USER' and 'PASSWORD' are the IDs of the user who will use the link to connect, which will inherit the permissions of all access through the link, and B is the name of the database's instance.

Using the DBLINK we can connect to the objects with the remote database's permissions that user has been provided in the creation statement.

To reference an object from the remote database should indicate the name of the object, concatenated with the character '@' and the name that we had given to the DBLINK.

Example: 

select * from TABLA@LNK_from_A_to_B 

The error ORA-30926 as a result of a Merge

Average: 3.3 (4 votes)

ORA-30926 error usually occurs when performing merge operations, and typically we leave something descolocados as the description of it does not give too much information about what is happening: 
ORA-30926: unable to get a stable set of rows in the source tables.

Normally this error occurs when the merge operation to target a row to be updated on it more than one row in the table source. As the engine does not know what to choose register returns an error. It is a problem of duplication in the origin table.

Example:

  • We have: 
    TABLA_ORIGEN with the values 
    ID Description 
    1 'The first value' 
    1 'The value with duplicate id' 
    2 'Another value'

Oracle Materialized views to optimize a Datawarehouse

Average: 4.5 (2 votes)

Datawarehouse loadings are periodic, and aggregate tables are useful to improve efficiency and the response time of our reports, so a physical optimisation resource that can bring big improvements in our system is the utilization of materialized views.

The materialized view is not more that a view, defined with one SQL sentence, of which in addition to storing his definition, store the data that returns, realizing an initial load and after a concrete time a refresh of the same.

Like this, if we have a Datawarehouse that updates daily, could use materialized views to go updating intermediate tables that feed our DWH diagrams, or directly to implement aggregate tables that will refresh from our base tables. 

The creation of this type of views aren't so complex as it can seem, the most important is to have clear the periodicity of the information update, and which method of refresh we will use.

Also will have to ensure us that our licence of database allows us use them (has to be a version Enterprise).

 

Syndicate content