49 votes

How to determine if an Oracle table is locked or not?

Following query gives details of all locks. SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID
user avatar
17 votes

How to determine if an Oracle table is locked or not?

The following script can be used in order quickly identify all lock objects within your Oracle system. select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b....
user avatar
  • 271
13 votes

Best approach for populating date dimension table

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip: Creating a date dimension or calendar table in SQL Server For the specific question at hand,...
user avatar
11 votes

Will SSRS be extinct soon, and PowerBI the new model?

Does this mean Microsoft will eventually try to deprecate SSRS? Ref: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/07/19/sql-server-2017-reporting-services-release-candidate-now-available/ No. ...
user avatar
  • 12.3k
7 votes
Accepted

Will SSRS be extinct soon, and PowerBI the new model?

About the separate installer for SSRS, here's a good reason: https://blogs.msdn.microsoft.com/sqlrsteamblog/2017/05/17/a-closer-look-at-power-bi-report-server/ In summary, SSRS, and PowerBI Report ...
user avatar
4 votes
Accepted

Search, filter and group across all columns in a tall and wide dataset quickly

Even if we generate a precomputed table with all of the columns in it (example: 80 columns, 300K rows), performing analysis on this is slow [...] Since we do not know ahead of time which fields would ...
user avatar
  • 71.2k
4 votes
Accepted

Modeling Conversion Events in a Data Warehouse

This is commonly represented with an accumulating snapshot fact table or timestamped accumulating snapshot fact table. Accumulating snapshot tables model events in progress for business processes that ...
user avatar
  • 824
4 votes
Accepted

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in ...
user avatar
4 votes

How to choose suitable naming convention for Dimensional Table Column in a Data Mart?

I just completed a 4 day course with the Kimball group and they had this to say on naming conventions: Always use full descriptive names ie. Brand Name. and don't use abbreviations. Otherwise you ...
user avatar
4 votes
Accepted

For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?

With hot_standby_feedback on, vacuum can still be done, but it will be less effective as some tuples that would otherwise be vacuumed now have to be delayed to a later vacuum. The only real downside ...
user avatar
  • 32.1k
4 votes

SQL Server + Microsoft Business Intelligence -- free version or online learning environment?

Evaluation Edition is Enterprise Edition with a timebomb (180 days). It includes SSIS, SSAS, SSRS, etc. So you can evaluate that for 180 days to learn the ins and outs of BI. Developer Edition, also, ...
user avatar
4 votes

Always On Availability Groups as a Secondary Read Only Database

The secondaries can be used as read only. Keep in mind data is still being added to the secondaries in near real time. Transaction log backups are conducted against the primary AG instance. If the ...
user avatar
  • 182
4 votes
Accepted

Always On Availability Groups as a Secondary Read Only Database

1.Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to? It'll be a full copy of the database. You can think ...
user avatar
3 votes
Accepted

Business Intelligence Development Studio installation for Visual studio 2010 problem

From your screenshot I judge that you are using the Express installer (It says: Select the express features to install), SSDT and Visual Studio 2010 have no influence on the options in this installer. ...
user avatar
  • 15.4k
3 votes
Accepted

Have a separate database for data warehousing?

In response to your reply to my comment, I mentioned some of the areas in which PostgreSQL shines for DW work - notably CTEs (Common Table Expressions - AKA the WITH clause) and Window functions (AKA ...
user avatar
3 votes
Accepted

2 SSRS Instances, one in native mode and one in sharepoint integrated mode with PowerPivot and PowerView on same server

So after messing with this off and on for a while I figured it out. It is possible. All this was done on one server with SharePoint 2013 enterprise and SQL Server 2012 Enterprise with SP2. I have a ...
user avatar
3 votes
Accepted

Is it good or bad practice to have a fact table relate to the same dimension table multiple times?

I'd use the same dimension table. Especially for a date dimension This is a Role Playing Dimension also here I would not let a client tool (or an Oracle forum post!) determine my SQL Server DW design....
user avatar
  • 68.9k
3 votes

How do I choose primary keys for star schema BI data warehouse?

Almost always surrogate keys (your "Auto Incrementing primary key") are best in data warehouses. I have seen very few exceptions where this is not the case (but some do exist) - yours does not seem to ...
user avatar
  • 1,596
3 votes

How to determine if an Oracle table is locked or not?

Using the below query you can find out locks on the table. column oracle_username format a15; column os_user_name format a15; column object_name format a37; column object_type format a37; select a....
user avatar
3 votes
Accepted

SSIS push and pull performance

SSIS is going to execute outside of SQL Server's address space. The data flow transformation within SSIS derives its performance through in-memory transformation to data. The more ram you can give it, ...
user avatar
  • 15.5k
3 votes
Accepted

SSDT - Unresolved Reference to temporary table in linked server

I know this is an old question, but I want to throw this out there in case anyone else happens upon this post. Create a "scripts" folder in your SQL Database Project, and add a Pre-Deploy script that ...
user avatar
  • 161
3 votes
Accepted

Star Schema from Relational Database

Ok, based on your very limited documentation, I would do the following: Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension ...
user avatar
3 votes

Import data into MySQL using Talend Open Studio

What parameters are You use for Talend tMySQLOutput? What MySQL settings? It could not be faster then INFILE, but still really depend from settings. You can post Your question on TelndForge forum ...
user avatar
  • 3,482
3 votes

Is ELT appropriate for life critcal applications such as Medical Systems when converting to a new application?

ELT is fine for a critical system. Your problem is predicated on this faulty assumption: "Because the data is not cleaned up like in ETL..." You can do most any cleanup with ELT as you do with ETL. ...
user avatar
  • 886
3 votes

Load New Foreign Key in Facts Table, When Update Dimension with SCD Type 2

No, you wouldn't update the fact table. The fact record is tied to the version of the customer which existed between 2018-04-02 11:52:47.040 and 2018-04-02 22:29:05.243 in which they had a 1.99 ...
user avatar
  • 15.5k
3 votes
Accepted

Is "ETL vs ELT" just "tables vs views"?

No. What you describe are all variants of ELT. The difference between ETL and ELT is in where you do the "T". The "traditional" ETL flow would implement the "T" (data ...
user avatar
  • 21.6k
2 votes

How do you roll up header-level values on a fact table with line-item granularity?

If at all possible, I would allocate the shipping cost to line items. This would let you assess the actual cost and profit for individual products as accurately as possible. If this is not possible, ...
user avatar
2 votes
Accepted

Business Logic in Data Warehouse

My answer is a big YES - you should store business logic in data warehouse. That is one of the ideas of data warehouses in the first place. If you have tens of reports showing or filtering leads ...
user avatar
  • 264
2 votes
Accepted

SQL Server 2012 Business Intelligence licensing

No, you do not have to buy a VS licence to use SSDT or SSDT-BI, you have the licence for the services (IS, AS, RS) if you have a SQL Server licence. SSDT and SSDT-BI are just tools to use those ...
user avatar
  • 941
2 votes

Data Warehouse attribute that describes its timeframe?

I would go with "the date range encompassed by the data warehouse is x." This eliminates all possible confusion about the intent by deliberately using words that are understandable by non-technical ...
user avatar
  • 67.2k

Only top scored, non community-wiki answers of a minimum length are eligible