need to to understand the physical structure of an OLAP database. And if I looked into a Oracle database, how can I tell if it's relational or OLAP? Isn't the OLAP data still stored in a table with keys that join to the other tables? I'm having a hard time picturing the structure and then I know querying is different too.

For years I heard these data warehouse jockeys talking about cubes, dimensions, and measures, craftily convincing the IT managers they know what they are talking about, and impressing the users that "this is the thing." But it always confused me. So I watched a few videos today, and the light bulb goes off - "oh, it's a pivot table".

btw, the cube is a nifty visual for a powerpoint presentation, but it's still presented in two-dimensions (piece of paper or a computer screen). Unless you have a 3-D printer I suppose ...

So I got the concept of measures, dimensions, etc., but now need to "get the picture" in what the data actually looks like and how to query it. I know this is bigger than one post here, but that's my objective.

For example, here's my confusion - I was also told today that an OLAP database is NOT relational in essence, it's totally different. In this article, you have
- the time dimension
- the product dimension
- the customer dimension
- the Fact table (total $ sales, quantity, discount)

BUT - don't all the dimension have keys that link it to the fact …

Hi Experts

 

How would you edit the following formula to count only Distinct items nested within the containsrow function...

 

OHA_OHP = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CaseType]="Face to Face"), DISTINCTCOUNT(Data[WorkType]),CONTAINSROW({"IMA OHP OffSite","OHP OnSite","IMA Network OHP","OHP Review 30 Minutes", "OHP Case Teleconference call (40 mins)"},Data[WorkType]))

hi,

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.

When communicating with SSAS msmdpump via SOAP requests Analysis services returns with Content-Types that are compressed and in binary XML (Content_type: xs+xpress).

We would like to include an extra layer of abstraction between the client and Analysis services and would like to know if there is a way to have Analysis services send back plain XML or how to read the xs+xpress content-types.

The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.

the two servers involved are sql 2014 and sql 2016
Our billing system is an OLTP database and it is very hard to report off of.   I have been reading on OLAP and ssas.  Is there a good manual or starting place for a rookie to try to overcome the reporting issues.
any ideas on should i start with trying to use ssas or creating an olap database from the online transactional database.

Power BI desktop and url connection to SSAS  cubes using the http msmdpump.dll. How to?

We have a url setup that gives a user access to our SSAS Cubes in Excel. That works fine no problem. (http://olap.mycubes.com)

But now we have a new user that needs to do the same thing in Power BI.

When I enter the URL in the prompt for server it gives me an "unsupported data format" error.

How do go about doing this for Power BI?

Hi,
I have invoice Header and Invoice Line Details fact Tables.
After creating Cube Invoice Header is working fine.
But Invoice Details not working properly, for example all are showing same value.(repeating)
In the invoice Header have customerID, But Invoice Details Don't have CustomerID.
Invoice Header Customer ID connected with DimCustomer,but invoiceline not connected with DimCustomer  Table.

Note: FactInvoiceHeader and FactInvoiceLine connected with Invoice Number
I tried to use Dimension Usage to connect but it's not working, Any idea how to connect with this

For example I have the below MDX query where it gives me all the data i want. however i would like to column slice all the 20K DWT's in one column and all the 25K DWT's in another column. Could I replace all the information in the FROM columns section with something like  [Voyage].[Vessel Tag].&[LIKE %20K DWT%] , [Voyage].[Vessel Tag].&[LIKE %25K DWT%] ON COLUMNS?

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Voyage].[Vessel Tag].[All]})}) ON COLUMNS ,
       NON EMPTY Hierarchize({DrilldownLevel({[Date].[Month].[All]})}) ON ROWS  
FROM (SELECT ({[Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, M],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, H],
               [Voyage].[Vessel Tag].&[FCCSP, 20K DWT, Eco, F],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCI, 20K DWT, Eco],
                     [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Marc],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Ho],
               [Voyage].[Vessel Tag].&[FCCSP, 25K DWT, Eco, Fra],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Ma],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Le],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco, Hy],
               [Voyage].[Vessel Tag].&[FCCI, 25K DWT, Eco]}) ON COLUMNS  
      FROM [cube])
WHERE …

Using SQL Server 2014 and I'm trying to merge Analytic data and Relational data for a report. I'm almost there but there are parts that need to be dynamic in the MDX part.

See attached query. I need the DATASOURCE dynamic, the Initial Catalog dynamic, and the year currently hardcoded as 2017 dynamic. I try to replace them with variables and it keeps giving me errors.
SQLMDXQuery.txt