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 ...
Business Intelligence is often a completely separate sect from Database Administration and Database Development. Business Intelligence, at the highest level, includes three main facets:
Reporting is the creation, deployment, and management of reports as well as the added ability for users to customize reporting ...
lots of Discussions about ETL vs ELT out there.
The main difference between ETL vs ELT is where the Processing happens
ETL processing of data happens in the ETL tool (usually record-at-a-time and in memory)
ELT processing of data happens in the database engine
Data is same and end results of data can be achieved in both methods.
it very much depends on ...
Is B.I. a business or technical project?
There are too many variables to answer this categorically; I'm tempted to VTC the question as it doesn't really have a single correct answer. However, on second thoughts I can say a few reasonably meaningful words on the subject.
Business Intelligence (or more prosaically reporting) is very ...
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.
The value depends very much on the individual organisation and its requirements. Depending on the level of sophistication required, a B.I. role might fall into a few different categories:
Spreadsheet jock - working from data sets extracted directly from operational systems, this role will produce reports and analysis using desktop tools such as Excel or ...
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 ...
Does this mean Microsoft will eventually try to deprecate SSRS?
We moved Reporting Services installation from the SQL Server installer
to a separate installer. This is a packaging change, not a product
change; access ...
It's almost a matter of semantics. A lot of hot air gets released in discussions about this but I'm not really convinced that there is any real philosophical depth to a distinction between the two.
At some level you can view ETL as transforming data in a client-side tool before finally loading it, with ELT implying that the data is transferred to some sort ...
The following script can be used in order quickly identify all lock objects within your Oracle system.
v$locked_object a ,
b.sid = a.session_id
a.object_id = c.object_id;
what would a BI person produce that is of value to my organization.
I'm going to take a stab at this part of the question as I think others have done a good job of explaining what BI is. I work for a company with many clients and I know a great deal of information about the functions we provide for those clients.
Our applications are very data-centric; ...
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 ...
While they are not great examples of best practice, the SQL Server sample databases would be good place to start. They include an OLTP, data warehouse and analysis services databases for a fictional organisation. Studying the differences between them should help you make sense of how OLTP (transaction) and OLAP (analytical/BI) databases differ and why.
Well...my solution is as follows:
I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.
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, ...
Kimball Group books are great source of DW knowledge and ETL information in particular.
Follow this link, click Tools and Utilities, scroll to Chapter 7 — Design and Develop the ETL System.
Download Sample SSIS packages contains full solutions for Adventure Works.
EAV is a nightmare for BI tools.
I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key.
However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the ...
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 ...
BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy
Databases designed with the assumption that they will be entirely resident in main memory can use structures such as T-tree indexes. But the real advantage is, IMDBs are just simpler. They do less (as they don't have to worry about managing a cache, or serializing writes for consistency, or anything to do with ACID-compliant I/O at all) so they execute fewer ...
Column store indexes will make an appearance in SQL Server 2012 (aka 'Denali').
Here is a link to a Power Point presentation by Conor Cunningham, Principal Software Architect in the SQL Server Query Processor team covering this new feature.
Not my field of expertise but as I understand it the difference in the majority of so-called in-memory OLAP databases (not a term I'm fond of, it's used as marketing pitch more than as a fair comparison of technologies) is column store indexes.
Column-Stores vs Row-Stores (How Different Are They Really) is a good intro to the technology if you're familiar ...
I would recommend reading The Data Warehouse Toolkit by Ralph Kimball. Goes through the theory, with some good examples, and will leave you with a solid understanding of the theory. Then you can look at the tools themselves.
If you're looking for something to build your ETL process, Pentaho have something called the "Star Modeller" which lets you model ...
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.
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).
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.
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 ...
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 ...