42

Think about a process or event that you want to analyze. Let's say you were building Lougle Analytics, and want to analyze visits (as opposed to single-page requests) to your site. Visiting a website is a process. A fact table represents a process or event you want to analyze, in this case it is a list of site visits. You can have as many fact tables as ...


40

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


14

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.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id; Reference:-...


13

In the image below which is an example of a basic Star Schema. The Dimensions are the Dim_Tables. These are generally the values that you want to analyse the data by. So you want to look at a particular product sales, in a particular country, over a particular date range. In the fact_sales table you have just the one Measure which is Units_Sold. Measures ...


12

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, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the ...


11

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. We moved Reporting Services installation from the SQL Server installer to a separate installer. This is a packaging change, not a product change; access ...


7

2 * 10k RAID1 for OS, 6 * 15k RAID10 for everything else. Honestly, with this many disks 1 array is the safest and usually fastest bet. If you've got time to test and have a real world, repeatable, measurable workload then by all means do a test run with your tempdb on the OS drive (caveat: limit tempdb file growth to ensure you don't splat the OS). Flip ...


7

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 Server has similar features hence it might be beneficial from the developer's perspective to group them accordingly (just a hunch, I am not a Microsoft employee, ...


5

Perfectly reasonable proposal. No technical issues from your description of what you're aiming for. I'm wary of commenting on licensing issues and would encourage you to verify you are correctly covered with either Microsoft or your reseller. In principle, the same licensing would apply for this arrangement as would apply if you were also deploying the ...


5

I recommend the Data Warehousing Toolkit by Ralph Kimball. This is a solid book on dimensional modeling and provides many "soup to nuts" case studies.


4

I'd introduce a magic Shipping product, and put the shipping value against this only (leaving open the possibility to have multiple shipping products too - Heavy / Overnight / etc). I'd probably still keep the ShippingAmount separate from the ProductAmount column though, so you can easily add up product sales only.


4

sys.dm_exec_requests is only able to display requests that are actually in flight at the precise moment sys.dm_exec_requests is executed. To capture all all requests that go by you need to hook into events, either XEvents or profiler events. Eg. capture RPC:Completed and SQL:BatchCompleted will capture all the queries executed (among other things).


4

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 be queried on the most, we cannot put indexes on specific columns. On the other hand, (correct me) I don't believe it's best to place 80 indexes on a table. ...


4

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 involve a predefined series of steps. The timestamped. You'll have a date key in the table that represents the date the opportunity hit each stage. If you ...


4

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 window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to ...


4

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, provides these tools for ~$50-60 (Microsoft sells it direct for $59.95, though you may find it cheaper from other retailers). The difference is no timebomb but ...


4

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 that I know of is the increased bloat. How much of a downside this is depends entirely on your usage. The worst case is if your database has small, intensively ...


4

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 secondary can not keep up with the volume of transactions coming across, it can cause the transaction logs on the primary to fill and continue to grow. In the ...


4

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 of it as mirrored but it's not really the same thing. 2.Does that break point in time transaction logs backup of the prod server? No. 3.Can this cause a ...


3

That's right. The cube browser control within SSDT is completely different to the one that used to exist in BIDS2008 or lower. This had me going at first too. The best way i found to get around this was to launch into excel, which can be done from the cube browser tab. I believe it even creates a temporary connection file for you too so you don't have to ...


3

I agree with Mark in that the ideal approach is to put the two slower drives in RAID 1 for O/S only, and the rest of the drives in RAID 10 for everything else. That would be ideal. Based on the sizes you gave, though, this pretty much maxes you out to start, with very little margin for error, and no room for growth. And by the way, if someone told you the ...


3

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 be exceptional. To answer why would be repeating stuff you can find all around the web, by the giants in the field, for instance: http://www.kimballgroup.com/...


3

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 may need to spend time training users. Don't use Dim or Fact in your table names because you end up spending a lot of time deleting these from the reporting layer....


3

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 default SQL server instance with all features selected, specifically the shared features "Reporting Services - Sharepoint" and "Reporting Services Add-in for ...


3

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.


3

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 the OVER clause). Without these in MySQL, you'll end up writing your own hacks to emulate these (and, no disrespect, but it'll take a while for your code to be ...


3

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. Make sure you install the Microsoft SQL Server2008 R2 RTM - Express with Advanced Services as that is the only one including BIDS. Have a look at Features ...


3

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, the better theoretical performance you can obtain. All things being equal between servers, I'd be surprised if there was an appreciable difference no ...


3

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 with screen short of Job components, will try to help For example one of my regular Talend-MySQL Jobs (billing prepare) - transfer from->to mysql 2M of records, ...


3

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. You just generally do it in a staging table on the target system instead of in-flight between the source and target. Edit: You asked why do they mention: ...


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