SSAS OLAP Cube Making Documentation
I want to prepare a documentation for olap cube. I need a schema for show the relationships. Is there any short way to do that?
See also questions close to this topic
SQL Server function returns question marks instead of real result
I have this function which retrieves first word from String:
CREATE FUNCTION dbo.FIRST_WORD(@value nvarchar(1000)) RETURNS nvarchar(1000) AS BEGIN RETURN CASE CHARINDEX(' ', @value, 1) WHEN 0 THEN @value ELSE SUBSTRING(@value, 1, CHARINDEX(' ', @value, 1) - 1) END END GO
The problem is the data in my table is in
non-ASCIIformat, so when i pass some value to that function, i get question marks instead of a result:
SELECT dbo.FIRST_WORD('ничего не поделаешь')
But if i pass
ASCIIcharacters, for example:
SELECT dbo.FIRST_WORD('hello world')
I tried to add
Nbefore the argument, but it didn't help:
SELECT dbo.FIRST_WORD(N'ничего не поделаешь')
SQL Server loop through a table for every 5 rows
I need to write a stored procedure or table function to return a new data table as a new data source. I wish to loop through the original table for every 5 rows base on the invoice ID column (it's possible not start from 1), the first 5 rows add to the left of the new table and the second 5 rows add to the right of the new table, the third 5 rows to the left and so on.
Thanks in advance!
ORACLE sql - JOINING TABLES
I need help with joining tables according to the ERD/Diagram provided. I did start it off alittle but I am having issues with joining the rest of the tables. This is what I did so far
SELECT first_name, last_name, username, post_title, comment_content FROM profile_info p JOIN club_member cm ON p.member_username = cm.username JOIN blog_post bp ON cm.username = bp.member_username JOIN post_comment pc ON bp.post_id = pc.post_id;
I want to join the POST_PROP, TAG_MEMBER_W_INTEREST and INTEREST. but i am so confused on how to do this. Please can someone help. Below is the diagram:
Also i am sorry I cannot post it other than a link because i do not have reputation points. If someone can edit this id appreciate it. Thank you.
UPDATE: I was able to join the 5 tables, now im left with two other tables which are POST_PROP and POST_COMMENT
--Profile info -> Club member -> Blog post -> TAG MEMBER INTEREST->INTEREST SELECT first_name, last_name, username, post_title,interest_tag FROM profile_info p JOIN club_member cm ON p.member_username = cm.username JOIN blog_post bp ON cm.username = bp.member_username JOIN tag_member_w_interest tg ON cm.username= tg.member_username JOIN interest i ON tg.interest_id = i.interest_id;
Write calculated member equivalent to scope assignment
I am trying to better understand scopes and calculated members better, so in the
AdventureWorksdatabase I did the following:
I wrote this simple scope statement:
SCOPE([Customer].[Customer].[All Customers], [Measures].[Average Rate]); /* This expression sets the value of the Amount measure */ THIS = 999; END SCOPE;
And equivalent calculated member will be this:
Create Member CurrentCube.[Measures].[My Measure] AS iif([Customer].[Customer].currentmember IS [Customer].[Customer].[All Customers], 999, [Measures].[Average Rate]);
But I am not sure how to create calculated member that will be equivalent to this scope assignment:
SCOPE([Customer].[Country].members, [Measures].[Average Rate]); /* This expression sets the value of the Amount measure */ THIS = 999; END SCOPE;
Actually I am not sure how to write
iffthat will check if current member of Customer dimension is a member of [Customer].[Country]
In a MS SSAS Project, creating a Data Source View over Npgsql (PostgreSQL) Data Source produces 42601: syntax error at or near '['
I'm trying to create Data Source View in a Multidimensional SSAS project using MS Visual Studio 2017 with SSDT installed. In the Data Source View Wizard, I select my Npgsql (v. 11.00) based datasource connecting my PostgreSQL 10.5 I created before. No matter which options I select or which tables I choose to work with in the dialogues that follows, after I click the final Finish button I always retrieve a message box stating that "42601: syntax error at or near '['" and preventing me from finishing the Data Source View. I assume the Visual Studio keeps treating the PostgreSQL the way it would treat MS SQL using [table].[column] notation instead of the "table"."column" one PostgreSQL would prefer. If so, is there a way how to correct this behaviour?
SSAS Tabular - Data in model, but grid blank
Having an issue with my model, which has been working well previously.
I'm currently using VS 2017 with SSDT. Now though, when I load the model, and process, it processes 1M rows as expected, but then the data grid shows blank fields. The rows are there, and when I select filter, the values show in the filter, but nothing on the actual data grid.
With the data not showing in the grid, it is affecting my calculated columns, telling me it can't work with blank values.
The compatibility level is 1400 and I'm deploying to an Azure Analysis Services server.
Has anybody come across this before?
Connect to SSAS OLAP Cube Using Python
Looking for resources to learn how to connect and fetch data from SSAS OLAP Cube.
Found I can do it with XMLA lib, but found no resources to explore and learn.
Syncfusion JS OLAP
Hi I am new with Syncfusion products, I work on Syncfusion JS on ASP.NET MVC on On https://help.syncfusion.com/js/pivotclient/olap-getting-started trying to Create a simple application with pivot client and OLAP data source (server mode), I already have all References they told later versions but it keep telling me that errors .. ANY HELP PLS!
PivotGrid' does not contain a definition for 'GetJsonData' and no extension method 'GetJsonData and deserializedReports. The type or namespace name 'Utils' does not exist in the namespace.
SSAS Dimension Attribute based on a SSAS calculated measure
I'm currently struggle with an issue and I hope you help with that.
I need to dynamically associate an attribute of a dimension in my SSAS cube (multidimensional) based on a calculation performed on the cube itself.
Simplifing, based on a date selected by the user, I have a calculation (already done) that returns the number of days that type of material is in stock. With that value I want to return an attribute based on a range of values.
Nr_Days_Calculated = 80
ID INI END DSC 1 0 90 TextA 2 91 180 TextB 3 181 99999 TextC
1 - TextA
Can anyone please help me? Thanks for your attention.
MSAS m2m attribute not resolved as expected
I'm no expert in MSAS Cube so may be this is obvious, but this is blocking an important feature in our team.
We have a fact table of "Indicators" (basicaly values from a calculator), that are computed for a specific date. indicators have a versionId, to group them following a functional rule.
It goes like :
From Date, Value, NodeId, VersionId D0 - 1.45 - N2 - V0
We have a fact table of "VersionsAssociation" that lists all the versions (the very same versions as the ones in the "Indicator" fact table) that are valid and visible and for what date.
To fit with a customer need, some versions are visible at multiple dates. For instance, a version computed for date D0, may be visible/recopied for date D1, D2, ...; so for a specific version V0, we would have in "VersionAssociation" :
VersionId , Date From (computed), Date To (Visible at what date) V0 - D0 - D0 V0 - D0 - D1 V0 - D0 - D2 V0 - D0 - D3
In our cube model, "Indicators" facts have a "From Date", the date they are compute for, but no "To Date", because when they are visible is not up to the indicator, but rather decided by the "VersionAssociation".
The means that in our "Dimension Usage" panel, we have a many-to-many relation from "Indicator" pointing to "VersionAssociation" on the dimension "To Date".
So far, this part works as expected. When we select "To Date" = D1 in Excel, we see indicators recopied from D0, with right values (no duplicate).
Then we have a thing called projection, where we split an indicator value alongside a specific dimension. For that we have a third measure group called "Projection", with values called "Weight".
Weights have a "To Date", because the weight are computed for a specific date, and even if an indicator is copied from D0 into D1, when projected, it is projected using D1 Weights.
Also we duplicate the weight regarding all the available from date, that's strange, but without it, the result are pure chaos.
Meaning we would have in the weights:
NodeId,From Date, To Date, Projection Axis, Weight N2 , D0 , D0 , P1 , 0.75 N2 , D0 , D0 , P2 , 0.25 (a value on node N2 would be split into 2 different values, where the sum is still the same) N2 , D0 , D1 , P1 , 0.70 N2 , D0 , D1 , P2 , 0.30
Here goes the issue:
- The Measure Group "Projection" and "Indicator" are directly linked to the dimension "Projection".
- "Projection" has a direct link to the "From Date" and the "To Date" dimension.
- "Indicator" has a direct link to the "From Date" dimension, but only a m2m reference to the "To Date" dimension, through the "Version Association" measure group.
To apply the Projection weights, we use a measure expression on the mesures from the "Indicator" Measure group, having something like "[Value Unit] * [Weight]".
Because of reasons, this causes MSAS to not properly disciminate the weight that are eligible to apply to a certain value in the "Indicator" measure group.
For instance, if we look into excel and ask for the D1 date (same behavior for all date), on the Projection Axsi P1 we got :
Value Weight 1.45 * 0.75 (Weight: From Date D0, To Date D0, P1) + 1.45 * 0.70 (Weight: From Date D0, To Date D1, P1)
for D1 and P2 we have :
Value Weight 1.45 * 0.25 (Weight: From Date D0, To Date D0, P2) + 1.45 * 0.30 (Weight: From Date D0, To Date D1, P2)
This cause the values to mean nothing and be non readable.
So what all of this is for, is to ask for a way to limit the weights that can be applied in the measure expression. We tried to use scope on "From Date" , "To Date" with the "Weight" measure or the "Value" measure, but the cube never step in our SCOPE instructions.
This is very long, and complicated, but we're stuck.
For each consumer find the cheapest product that was purchased
I am trying to obtain a list of the cheapest product that was purchased for by each consumer from a cube. I tried the following cube query:
SELECT "Consumer Name","Product Name",MIN([Total Price]) AS "Total Purchase Price" FROM Transaction_Cube WHERE "Consumer Name" IS NOT NULL AND "Consumer City" IS NOT NULL AND "Consumer State" IS NOT NULL AND "Product Name" IS NOT NULL AND "Product Category" IS NOT NULL AND "Product Line" IS NOT NULL AND "Product Packaging" IS NOT NULL GROUP BY "Consumer Name","Product Name"
But that is giving me the following results:
where I am getting the lowest purchased price of each consumer/product combination. But what I am trying to achieve is something like this:
where I have for each consumer only the cheapest product that was purchased. Any ideas or suggestions on how to correct this query would be truly appreciated!
Analysis Services Cube Name
I have a multidimensional cube in SSAS 2012 (upgrading to newer SQL version soon), and now need to rename the cube. Unfortunately by doing so, all other reports and systems which read from this cube will break and need to be fixed to reflect the new cube name. Is there a way (or workaround) to somehow define an alias for the cube name so that the old reports can still refer to the cube using the old name?